PostgreSQL - jsonb_each

Question

I just started playing jsonb on postgres and find examples that are hard to find on the Internet, as this is a relatively new concept. I am trying to use jsonb_each_text to print out a table of keys and values, but I get csv in one column.

I have a junior json saved as jsonb and using it to validate my requests.

{ "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 access the offer_currencies array using

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

which gives the result "{" value ": 1220.42," currency_code ":" EUR "}", so if I run the following query that I get (I need to change the "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 it prints csv in one column

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

Answer 1

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

But : not SELECT set-return functions, which can often lead to errors (or unexpected results). Use f.ex. instead. LATERAL joins / subqueries:

 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 are implicit LATERAL unions (here: CROSS JOIN s).