Search code examples
postgresqljsonbpostgresql-9.5

Fetch rows from postgres table which contains a specific id in jsonb[] column

I have a details table with adeet column defined as jsonb[]

a sample value stored in adeet column is as below image

Sample data stored in DB :

enter image description here

I want to return the rows which satisfies id=26088 i.e row 1 and 3

I have tried array operations and json operations but it does'nt work as required. Any pointers

Solution

  • Obviously the type of the column adeet is not of type JSON/JSONB, but maybe VARCHAR and we should fix the format so as to convert into a JSONB type. I used replace() and r/ltrim() funcitons for this conversion, and preferred to derive an array in order to use jsonb_array_elements() function :

    WITH t(jobid,adeet) AS
(
 SELECT jobid, replace(replace(replace(adeet,'\',''),'"{','{'),'}"','}')   
   FROM tab 
), t2 AS
(
SELECT jobid, ('['||rtrim(ltrim(adeet,'{'), '}')||']')::jsonb as adeet 
  FROM t
)
SELECT t.*
  FROM t2 t
 CROSS JOIN jsonb_array_elements(adeet) j
 WHERE (j.value ->> 'id')::int = 26088

    Demo