Cannot unnest json in postgres

Question

create table json_example as
select '[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]'::jsonb as value

select * from json_example

the following is working fine:

select * from json_array_elements('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]')

And i want to do the same, only difference that json is coming from a table. how to do the same using json_example table value field?

update: i have jsonb data

clairvoyant
– clairvoyant

2019-04-06 15:02:50 +00:00
Commented Apr 6, 2019 at 15:02 — clairvoyant
– clairvoyant, Commented Apr 6, 2019 at 15:02

Kaushik Nayak · Accepted Answer · 2019-04-06 14:44:08Z

2

cross join json_array_elements with the table

select j.* from json_example cross join
json_array_elements(value) as j

DEMO

answered Apr 6, 2019 at 14:44

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

12 Comments

clairvoyant Over a year ago

Query execution failed Reason: SQL Error [XX000]: ERROR: failed to find conversion function from unknown to json

Kaushik Nayak Over a year ago

@clairvoyant : As I've shown in the demo, you should cast the column to json if you're using a ctas . It'll be created as unknown by default even if you've used a valid json

clairvoyant Over a year ago

yes like that its work, porblem is that i have an existing table where values is a jsonb. is there something similar solution for that?

Kaushik Nayak Over a year ago

@clairvoyant : You should have told that. In that case you may use jsonb_array_elements

clairvoyant Over a year ago

select j.* from json_example cross join jsonb_array_elements(value) as j . still gives me error: Reason: SQL Error [22023]: ERROR: cannot extract elements from an object

|

Collectives™ on Stack Overflow

Cannot unnest json in postgres

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related