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

pozs · Accepted Answer · 2015-03-04 15:11:45Z

53

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

answered Mar 4, 2015 at 15:11

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Over a year ago

Can you recommend any resources for learning idioms like that ("don't SELECT set-returning functions, that can often lead to errors")?

Escalen Over a year ago

jsonb_each_text returns a set of records. Records are a case of a 'composition-type'. Here are the official docs on how you should handle composition types in queries: postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

jian · Accepted Answer · 2021-09-29 15:00:15Z

1

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.

answered Sep 29, 2021 at 15:00

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - jsonb_each

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related