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

score 48 · Accepted Answer · answered Mar 04 '15 at 15:11

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

Can you recommend any resources for learning idioms like that ("don't SELECT set-returning functions, that can often lead to errors")? — Jay, Nov 07 '15 at 05:31

score 0 · Answer 2 · answered Sep 29 '21 at 15:00

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.

PostgreSQL - jsonb_each

2 Answers2

Linked