PostgreSQL - jsonb_each

I have just started to play around with jsonb on postgres and finding examples hard to find online as it is a relatively new concept.I am trying to use jsonb_each_text to printout a table of keys and values but get a csv's in a single column.

I have the below json saved as as jsonb and using it to test my queries.

{ "lookup_id": "730fca0c-2984-4d5c-8fab-2a9aa2144534", "service_type": "XXX", "metadata": "sampledata2", "matrix": [ { "payment_selection": "type", "offer_currencies": [ { "currency_code": "EUR", "value": 1220.42 } ] } ]
}

I can gain access to offer_currencies array with

SELECT element -> 'offer_currencies' -> 0
FROM test t, jsonb_array_elements(t.json -> 'matrix') AS element
WHERE element ->> 'payment_selection' = 'type'

which gives a result of "{"value": 1220.42, "currency_code": "EUR"}", so if i run the below query I get (I have to change " for ')

select * from jsonb_each_text('{"value": 1220.42, "currency_code": "EUR"}')
Key | Value
---------------|----------
"value" | "1220.42"
"currency_code"| "EUR"

So using the above theory I created this query

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;

But this prints csv's in one column

record
---------------------
"(value,1220.42)"
"(currency_code,EUR)"

2 Answers

The primary problem here, is that you select the whole row as a column (PostgreSQL allows that). You can fix that with SELECT (jsonb_each_text(data)).* ....

But: don't SELECT set-returning functions, that can often lead to errors (or unexpected results). Instead, use f.ex. LATERAL joins/sub-queries:

select first_currency.*
from test t , jsonb_array_elements(t.json -> 'matrix') element , jsonb_each_text(element -> 'offer_currencies' -> 0) first_currency
where element ->> 'payment_selection' = 'type'

Note: function calls in the FROM clause are implicit LATERAL joins (here: CROSS JOINs).

WITH testa AS( select jsonb_array_elements (t.json -> 'matrix') -> 'offer_currencies' -> 0 as jsonbcolumn from test t)
SELECT d.key, d.value FROM testa join jsonb_each_text(testa.jsonbcolumn) d ON true
ORDER BY 1, 2;

tetsa get the temporal jsonb data. Then using lateral join to transform the jsonb data to table format.