CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpostgresql-json
Balinti
Balinti

Reputation: 1534

Exracting Fields from JSON array

My Table 

create table i (a json)

Insert:

insert into i values ('[[1300,[{"id":5,"time":1451423706048,"zon":0,"name":"AMighty","loc":"ny"},[[3405,0,1000],[35,0,1000],[3401,0,10000],[2541,0,1000]]]]]' )

I wish to extract the field which comes after 2541. in that case the answer 0

When querying my Json with:

select json_array_elements(a) from I

it returns only one row.

Upvotes: 0

Views: 83

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

You need to go through all the nested arrays to get necessary element:

WITH i(a) AS ( VALUES
  ('[[1300,[{"id":5,"time":1451423706048,"zon":0,"name":"AMighty","loc":"ny"},[[3405,0,1000],[35,0,1000],[3401,0,10000],[2541,0,1000]]]]]'::JSON)
)
SELECT
  array_element->1->1->3 as array_you_look_for,
  array_element->1->1->3->1 as inner_array_element
FROM i,
  json_array_elements(a) as array_element;

Output:

 array_you_look_for | inner_array_element 
--------------------+---------------------
 [2541,0,1000]      | 0
(1 row)

Upvotes: 2

Related Questions