Exracting Fields from JSON array

Question

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.

Dzmitry Savinkou · Accepted Answer · 2016-01-07 12:39:51Z

2

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)

answered Jan 7, 2016 at 12:39

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

11 Comments

koriander Over a year ago

can you clarify this syntax: FROM i, json_array_elements(a) as array_element; what relation is created?

Dzmitry Savinkou Over a year ago

what do you mean? after FROM clause you can specify one or more sources to select from - check postgresql.org/docs/9.4/static/sql-select.html @koriander

koriander Over a year ago

I'm not familiar with postgresql specifities and that page is not clear on this point, but the comma is usually a cartesian join, so the second part should be a relation. But how come json_array_elements(a) knows it refers to field 'a' of table i.

koriander Over a year ago

and why not simply do SELECT a->0->1->1->3->1 from i

Balinti Over a year ago

Dmitry, Is there is away which I can take the 2541 as a field and not by location?

|

Collectives™ on Stack Overflow

Exracting Fields from JSON array

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related