How to extract key and value from json column in Postgres?

Question

I want to add key-value pairs extracted from json column to table with Postgresql.

I have an items_table with uid and data column in json with multiple pairs. Initial table that looks like:

uid | data

1   | {"item1":"string1", "item2":"string2"}
2   | {"item2":"string3", "item5":"string1", "item7":"string5"}
3   | {"item1":"string3", "item4":"string1", "item6":"string8", "item5":"string5"}
4   | {"item1":"string4"}

...

Thus, some items/strings can repeat and fields length can vary as well.

I tried apply jsonb_object_keys, mentioned in documentation, however I occurred an error.

     select jsonb_object_keys(data) 
     from items_table;

ERROR: function jsonb_object_keys(character varying) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

I would like obtain a table result in following way to get split by items and strings, expanding uid column:

uid | items      | strings

1   | item1      | string1
1   | item2      | string2
2   | item2      | string3
2   | item5      | string1
2   | item7      | string5
3   | item1      | string3
3   | item4      | string1
3   | item6      | string8
3   | item5      | string5
4   | item1      | string4

How is it possible to achieve the above output?

Barbaros Özhan · Accepted Answer · 2020-09-10 06:32:29Z

2

You can use json_each_text() method to easily split jsonb type column to two seperate columns :

select uid, (js).key as items, (js).value as strings
  from
  (
   select uid, jsonb_each_text(data) as js
     from tab
  ) q

or more directly by using cross join :

select uid, js.items, js.value as strings
  from tab
 cross join jsonb_each_text(data) as js(items)

Demo

edited Sep 10, 2020 at 6:32

answered May 16, 2019 at 18:27

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-05-16 18:13:01Z

1

You should be able to extract the keys and then the values:

select key, v.j->key
from (values (1, '{"item1":"string1", "item2":"string2"}'::jsonb),
             (2, '{"item2":"string3", "item5":"string1", "item7":"string5"}'::jsonb)
     ) v(id, j) cross join lateral
     jsonb_object_keys(v.j) as key;

Here is a db<>fiddle.

answered May 16, 2019 at 18:13

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to extract key and value from json column in Postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related