0
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?

clairvoyant
  • 195
  • 1
  • 11

1 Answers1

2

cross join json_array_elements with the table

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

DEMO

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Query execution failed Reason: SQL Error [XX000]: ERROR: failed to find conversion function from unknown to json – clairvoyant Apr 06 '19 at 14:48
  • @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` – Kaushik Nayak Apr 06 '19 at 14:49
  • 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? – clairvoyant Apr 06 '19 at 14:53
  • @clairvoyant : You should have told that. In that case you may use `jsonb_array_elements` – Kaushik Nayak Apr 06 '19 at 14:54
  • 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 – clairvoyant Apr 06 '19 at 14:56
  • @clairvoyant : That error doesn't look like a Postgres error. See [this](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=e040885ce68daa090e7b7ddc11b362f6) it works fine for me. I don't see why it should give you that error. – Kaushik Nayak Apr 06 '19 at 15:07
  • @clairvoyant : You're welcome. It'd be great if you also tell us why it wasn't working earlier. It would definitely help a future answer seeker facing same kind of error. – Kaushik Nayak Apr 06 '19 at 16:02
  • good question, must be some silly error no worries on that. simply copy paste it again and it worked. – clairvoyant Apr 06 '19 at 17:02
  • is there a solution where it is a left join? i am querying into a nested json and when the nested json field contains: [] then it returns no rows – clairvoyant Apr 06 '19 at 17:10
  • is my field: value->'fields'->'subtasks' and when it contains [] it returns no rows so thats why i need something left join – clairvoyant Apr 06 '19 at 17:15
  • @clairvoyant : A kind suggestion - do not use comment section of an answer for further requirements / details. They should be posted as a **separate question** with specific details. – Kaushik Nayak Apr 06 '19 at 17:19
  • you are right. i have opened a new question for that: https://stackoverflow.com/questions/55551953/jsonb-array-elements-does-not-return-row-when-not-found – clairvoyant Apr 06 '19 at 18:04