Query for array elements inside JSON type

Question

I m trying to test out the json type in PostgreSQL 9 3  I have a json column called data in a table called reports  The JSON looks something like this        objects           src   foo png          src   bar png           background   background png      I would like to query the table for all reports that match the  src  value in the  objects  array  For example  is it possible to query the DB for all reports that match  src     foo png   I successfully wrote a query that can match the  background    SELECT data AS data FROM reports where data- gt  gt  background     background png    But since  objects  has an array of values  I can t seem to write something that works  Is it possible to query the DB for all reports that match  src     foo png   I ve looked through these sources but still can t get it    http   www postgresql org docs 9 3 static functions-json html How do I query using fields inside the new PostgreSQL JSON datatype  http   michael otacoo com postgresql-2 postgres-9-3-feature-highlight-json-operators    I ve also tried things like this but to no avail   SELECT json array elements data- gt  objects   AS data from reports WHERE  data- gt  gt  src     foo png     I m not an SQL expert  so I don t know what I am doing wrong

User · Accepted Answer

jsonb in Postgres 9 4  You can use the same query as below  just with jsonb array elements    But rather use the jsonb  quot contains quot  operator   gt  in combination with a matching GIN index on the expression data- gt  objects   CREATE INDEX reports data gin idx ON reports USING gin   data- gt  objects   jsonb path ops    SELECT   FROM reports WHERE data- gt  objects    gt      quot src quot   quot foo png quot       Since the key objects holds a JSON array  we need to match the structure in the search term and wrap the array element into square brackets  too  Drop the array brackets when searching a plain record  More explanation and options   Index for finding an element in a JSON array  json in Postgres 9 3  Unnest the JSON array with the function json array elements   in a lateral join in the FROM clause and test for its elements  SELECT data  text  obj FROM   reports r  json array elements r data  gt   objects    obj WHERE  obj- gt  gt  src     foo png   db lt  gt fiddle here Old sqlfiddle The CTE  WITH query  just substitutes for a table reports  Or  equivalent for just a single level of nesting  SELECT   FROM   reports r  json array elements r data- gt  objects   obj WHERE  obj- gt  gt  src     foo png   - gt  gt   - gt  and   gt  operators are explained in the manual  Both queries use an implicit JOIN LATERAL  Closely related   Query for element of array in JSON column

User · Answer

Create a table with column as type json  CREATE TABLE friends   id serial primary key  data jsonb     Now let s insert json data  INSERT INTO friends data  VALUES     name    Arya    work     Improvements    Office     available   true     INSERT INTO friends data  VALUES     name    Tim Cook    work     Cook    ceo    Play     uses     baseball    laptop     available   false       Now let s make some queries to fetch data  select data- gt  name  from friends  select data- gt  name  as name  data- gt  work  as work from friends    You might have noticed that the results comes with inverted comma      and brackets            name                 work             ------------ ----------------------------   Arya          Improvements    Office     Tim Cook      Cook    ceo    Play    2 rows    Now to retrieve only the values just use - gt  gt    select data- gt  gt  name  as name  data- gt  work - gt  gt 0 as work from friends  select data- gt  gt  name  as name  data- gt  work - gt  gt 0 as work from friends where data- gt  gt  name   Arya

[sql] Query for array elements inside JSON type

`jsonb` in Postgres 9.4+

`json` in Postgres 9.3+

Examples related to sql

Examples related to json

Examples related to postgresql

Examples related to jsonb

Examples related to lateral