Check a value in an array inside a object json in PostgreSQL 9.5

Question

I have an json object containing an array and others properties.

I need to check the first value of the array for each line of my table.

Here is an example of the json

{"objectID2":342,"objectID1":46,"objectType":["Demand","Entity"]}

So I need for example to get all lines with ObjectType[0] = 'Demand' and objectId1 = 46.

This the the table colums

id |  relationName | content

Content column contains the json.

Vao Tsun · Accepted Answer · 2017-09-27 10:38:57Z

1

just query them? like:

t=# with table_name(id, rn, content) as (values(1,null,'{"objectID2":342,"objectID1":46,"objectType":["Demand","Entity"]}'::json))
select * From table_name
where content->'objectType'->>0 = 'Demand' and content->>'objectID1' = '46';
 id | rn |                              content
----+----+-------------------------------------------------------------------
  1 |    | {"objectID2":342,"objectID1":46,"objectType":["Demand","Entity"]}
(1 row)

answered Sep 27, 2017 at 10:38

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Check a value in an array inside a object json in PostgreSQL 9.5

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related