how to insert the attributes of a postgres jsonb field into a key-value table?

Question

I want to turn jsonb values into a key-value table within a single statement

My sample code:

CREATE TABLE public.searchdatacache
( querysig text, "values" jsonb );
INSERT INTO searchdatacache (querysig, values)
VALUES 
(
'ytApiSearch://Armageddon trailer',
'[
  {"VideoId"      : "xwseawq"},
  {"Title"        : "Armageddon"},
  {"PublishedAt"  : "2012/01/01"},
  {"Description"  : "Armageddon is a film"},
  {"ChannelTitle" : "Bruce Willis Movies"}
]'
)

SELECT jsonb_array_elements(values) from searchdatacache where querysig =    'ytApiSearch://Armageddon trailer'

returns:

"{"VideoId": "xwseawq"}"
"{"Title": "Armageddon"}"
"{"PublishedAt": "2012/01/01"}"
"{"Description": "Armageddon is a film"}"
"{"ChannelTitle": "Bruce Willis Movies"}"

the data returned by the select statement looks ok so far but now I want to use this select statement within an "INSERT INTO" statement to fill a key-value table.

these records should be filled into a new key-value table:

VideoId      | xwseawq
Title        | Armageddon
PublishedAt  | 2012/01/01
Description  | Armageddon is a film
ChannelTitle | Bruce Willis Movies

thanks in advance Gerald

Kaushik Nayak · Accepted Answer · 2019-03-26 03:25:27Z

1

Use jsonb_array_elements in the from clause along with jsonb_each_text to get the key value pairs ready to be inserted.

select s.key,s.value
       from searchdatacache cross join lateral
             jsonb_array_elements(values) as j(e)
    cross join lateral jsonb_each_text(j.e) as s(key,value);

Demo

answered Mar 26, 2019 at 3:25

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

how to insert the attributes of a postgres jsonb field into a key-value table?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related