CODEWITHSUNDEEP
sqljsonpostgresql
Ashesh Khatri
Ashesh Khatri

Reputation: 162

Fetching Value from Json Array Object in Postgres

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

Upvotes: 0

Views: 1608

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51406

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:

  1. use json_array_elements to divide array for check.
  2. use json_object_keys toget the key of divided array element
  3. 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:

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

Upvotes: 1

Anshuman Jaiswal
Anshuman Jaiswal

Reputation: 5462

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

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

Upvotes: 0

Related Questions