Fetching Value from Json Array Object in Postgres

Question

I'm new to Postgres so I'm stuck while creating a query.

The table definition:

id - primary key,
data - JSON

Sample data:

id       data
--------------------------------------------------------------
1           [{"279863":"500040004","awb_no":"18171917033930"},{"279864":"500040003","awb_no":"18171917033931"}]

I want to find the key (279864) exists in my data column using where clause

Vao Tsun · Accepted Answer · 2018-05-29 07:20:16Z

1

t=# with c(id,data) as (values(1,'[{"279863":"500040004","awb_no":"18171917033930"},{"279864":"500040003","awb_no":"18171917033931"}]'::json))
select id,json_object_keys(json_array_elements(data)) = '279864' from c;
 id | ?column?
----+----------
  1 | f
  1 | f
  1 | t
  1 | f
(4 rows)

so you can check with WHERE EXISTS or count(*) > 0 or any another way you like...

eg, with bool_or (if at least one is true, group is true):

t=# with c(id,data) as (values(1,'[{"279863":"500040004","awb_no":"18171917033930"},{"279864":"500040003","awb_no":"18171917033931"}]'::json))
, m as (select id,(json_object_keys(json_array_elements(data)) = '279864')j from c)
select id, bool_or(j) from m group by id;
 id | bool_or
----+---------
  1 | t
(1 row)

So in short:

use json_array_elements to divide array for check.
use json_object_keys toget the key of divided array element
use bool_or to check if at least one key is like pattern

update as OP is asking for "less complicated" solution, I post a monkey hack as well:

t=# with c(id,data) as (values(1,'[{"279863":"500040004","awb_no":"18171917033930"},{"279864":"500040003","awb_no":"18171917033931"}]'::json))
select * from c where data::jsonb::text ~ '(,)|({ )"279863":';
 id |                                                data
----+-----------------------------------------------------------------------------------------------------
  1 | [{"279863":"500040004","awb_no":"18171917033930"},{"279864":"500040003","awb_no":"18171917033931"}]
(1 row)

which is of course is very slippery and requires some explanation as well:

I need to cast to jsonb first to eliminate possible syntax freedom
json object keys are not sorted, thus I need to catch both {{ and , cases

edited May 29, 2018 at 7:20

answered May 28, 2018 at 10:05

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

3 Comments

Ashesh Khatri Over a year ago

any other way to handle this?

Ashesh Khatri Over a year ago

currently, my query already working with joins and group by and it was already complicated. So is there any other way to handle it.

Vao Tsun Over a year ago

ah, I see. no - fetching array elements requires unnesting the array, which will require grouping after the check - other way is quite a monkey hack - I'll post it as alternative

Anshuman Jaiswal · Accepted Answer · 2018-05-28 10:07:10Z

0

make data column as JSONB and then you can easily do by using:

SELECT * FROM table WHERE data->>'279863' IS NOT NULL;

answered May 28, 2018 at 10:07

Anshuman Jaiswal

5,4621 gold badge31 silver badges46 bronze badges

1 Comment

Ashesh Khatri Over a year ago

I cannot change this value bcoz it's my live application, any other way to get this data?

Collectives™ on Stack Overflow

Fetching Value from Json Array Object in Postgres

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related