PostgreSQL – Finding Rows with a Key in a JSONB Array

arrayindexjsonjson-pathpostgresql

I'm trying to query for a key present in an array of objects. This structure:

column jdata
{"name": "Somedata",
 "array": [ {"name":"bla1", "attr": "somevalue"}, 
            {"name":"bla2", "otherdata": "somevalue2"},
            {"name":"bla3", "otherdata": "somevalue"}
           ],
"otherstuff": "stuff"
}

Now I do btree's on jdata->'name' or (jdata->'datetime')::cast and that works great.

I also do json_path_ops where jdata->'array' @> '[{"name":"bla3"}]' works a real charm.

My problem is the attr key can be in any of the objects in the array and I care about the record if the key is present, however the value can be nearly anything. Is there a way to query for this? Is there a way it can be indexed? I want to do jdata->'array' @> '[{"attr": ?}]' Or maybe the ? 'attr' can be used inside an array somehow?

Currently I am thinking a trigger that scans for the key and then moves it to a header with a true or false or whatever, and then a normal btree will work. Is there a better way? I need to edit around 500k records at the average site to add this values.

Please point me in a direction.

Best Answer

Postgres 12 or later: with SQL/JSON path expression

SELECT *
FROM   tbl
WHERE  jdata->'array' @? '$ ? (exists (@."attr"))';

You can look for keys or values, only in the outer nesting level or recurse, abstract arrays away (unnest them) in lax mode or not (strict mode). I added query variations in the fiddle to demonstrate:

db<>fiddle here

@? is jsonpath existence operator

jsonpath value '$ ? (exists (@."attr"))' explained:

$ ... look at each value found in left operand (in default "lax" mode)
? ... run the following test
(exists (@."attr")) ... Does a key with name 'attr' exist?

Can use an index, either with default jsonb_ops:

CREATE INDEX tbl_jdata_array_idx ON tbl USING GIN ((jdata->'array'));

Or with jsonb_path_ops:

CREATE INDEX tbl_jdata_array_path_ops_idx ON tbl USING GIN ((jdata->'array') jsonb_path_ops);

See:

How to sum the values of a json column filtered with regex?

Postgres 11 or older (no SQL/JSON, yet)

That particular use case is not covered by plain indexes for built-in operators.

Simple query without index support

SELECT *
FROM   tbl
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(jdata->'array') elem
   WHERE  elem ? 'attr' 
   );

EXISTS because we want each qualifying row once, even if multiple array elements can contain the key. And it's faster.
But this query cannot use an index.

Expression index

You can generate a text array of unique keys in the given jsonb array of records - and wrap the expression into a simple IMMUTABLE function:

CREATE OR REPLACE FUNCTION jsonb_arr_record_keys(jsonb)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY (
   SELECT DISTINCT k
   FROM   jsonb_array_elements($1) elem, jsonb_object_keys(elem) k
   )';

COMMENT ON FUNCTION jsonb_arr_record_keys(jsonb) IS '
   Generates text array of unique keys in jsonb array of records.
   Fails if any array element is not a record!';

Then create a GIN expression index based on this function:

CREATE INDEX tbl_special_idx ON tbl USING gin (jsonb_arr_record_keys(jdata->'array'));

Query like this, using the generic array contains operator @>:

SELECT *
FROM   tbl
WHERE  jsonb_arr_record_keys(jdata->'array') @> '{attr}';

Now the index can be used efficiently.

Provide the key name nested in an array ('{attr}'). (You can conveniently check for multiple keys this way ('{attr1, attr2}') or similar ... )

dbfiddle here

Mysql – See if an JSON array in MySQL contains an object whose key holds a specific date

One solution as provided by Morgan Tucker - @morgo is to use json_contains like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}')

For now the answer is OK, but i believe it can have some performance issues and feels a bit hackish to me (see the next query) - but I will deal with those when i get there :)

If I would need to query on a date range (from 2016-04-24 to 2016-04-26) I would need to add an invididual json_contains for each day in the time span like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}') or json_contains(`data`, '{"date" : "2016-04-25"}') or json_contains(`data`, '{"date" : "2016-04-24"}')

And this would return invalid data if I would have an date key nested somewhere else

So if you have an different solution, I would like to know

Building JSON Object from One-to-Many Relationship Data in PostgreSQL

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle:

select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds"
from container

1.

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, json_agg(thing_id) AS "thingIds"
   FROM   container c
   LEFT   JOIN container_thing ct ON  ct.container_id = c.id
   WHERE  c.id IN (<list of container ids>)
   GROUP  BY c.id
   ) sub;

2.

With more than a few rows per container (you mentioned 20) it's typically faster to aggregate before you join:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN (
      SELECT container_id AS id, json_agg(thing_id) AS "thingIds"
      FROM   container_thing
      WHERE  container_id IN (<list of container ids>) -- repeat condition
      GROUP  BY 1
      ) ct USING (id)
   WHERE  c.id IN (<list of container ids>)
   ) sub;

3.

Or you can combine the ARRAY constructor you found with LEFT JOIN LATERAL:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN LATERAL (
      SELECT ARRAY (
         SELECT thing_id
         FROM   container_thing
         WHERE  container_id = c.id
         -- ORDER  BY thing_id  -- optional order for deterministic results
         ) AS "thingIds"
      ) ct ON true
   WHERE  c.id IN (<list of container ids>)
   ) sub;

What is the difference between LATERAL and a subquery in PostgreSQL?

Might be faster, yet.

SQL Fiddle. (Extending @a_horse's fiddle.)

Note that the result for empty containers is subtly different in the three queries above:

"thingIds":[null]
"thingIds":null
"thingIds":[]

4.

In Postgres 9.5 (since you are using it) you could also work with jsonb and its functionality and one less subquery:

SELECT jsonb_set(to_jsonb(c), '{thingIds}', "thingIds") AS container_with_things
FROM   container c
LEFT   JOIN (
   SELECT container_id AS id, jsonb_agg(thing_id) AS "thingIds"
   FROM   container_thing
   WHERE  container_id IN (<list of container ids>) -- repeat condition
   GROUP  BY 1
   ) ct USING (id)
WHERE  c.id IN (<list of container ids>);

Alternatively:

SELECT to_jsonb(c) || jsonb_build_object('thingIds', "thingIds") AS container_with_things
FROM   ...