我刚刚开始在postgres上使用jsonb,在网上很难找到例子,因为这是一个相对较新的概念.我试图使用jsonb_每个_文本打印出一个键和值表,但在单个列中得到一个csv.
我将下面的json另存为jsonb,并使用它来测试我的查询.
{
"lookup_id": "730fca0c-2984-4d5c-8fab-2a9aa2144534",
"service_type": "XXX",
"metadata": "sampledata2",
"matrix": [
{
"payment_selection": "type",
"offer_currencies": [
{
"currency_code": "EUR",
"value": 1220.42
}
]
}
]
}
我可以通过
SELECT element -> 'offer_currencies' -> 0
FROM test t, jsonb_array_elements(t.json -> 'matrix') AS element
WHERE element ->> 'payment_selection' = 'type'
它给出了一个"{"value:1220.42,"currency_code:"EUR"}"的结果,所以如果我运行下面的查询,我会得到(我必须更改为"for")
select * from jsonb_each_text('{"value": 1220.42, "currency_code": "EUR"}')
Key | Value
---------------|----------
"value" | "1220.42"
"currency_code"| "EUR"
因此,利用上述理论,我创建了这个查询
SELECT jsonb_each_text(data)
FROM (SELECT element -> 'offer_currencies' -> 0 AS data
FROM test t, jsonb_array_elements(t.json -> 'matrix') AS element
WHERE element ->> 'payment_selection' = 'type') AS dummy;
但这是一列
record
---------------------
"(value,1220.42)"
"(currency_code,EUR)"