PostgreSQL - jsonb_each

Question

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)" 

Answer 1

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).