Search code examples
jsonpostgresqlpostgresql-9.3

Query Postgres for number of items in JSON

I am running Postgres 9.3 and have a problem with a query involving a JSON column that I cannot seem to crack.

Let's assume this is the table:

# CREATE TABLE aa (a int, b json);
# INSERT INTO aa VALUES (1, '{"f1":1,"f2":true}');
# INSERT INTO aa VALUES (2, '{"f1":2,"f2":false,"f3":"Hi I''m \"Dave\""}');
# INSERT INTO aa VALUES (3, '{"f1":3,"f2":true,"f3":"Hi I''m \"Popo\""}');

I now want to create a query that returns all rows that have exactly three items/keys in the root node of the JSON column (i.e., row 2 and 3). Whether the JSON is nested doesn't matter.

I tried to use json_object_keys and json_each but couldn't get it to work.

Solution

  • json_each(json) should do the job. Counting only root elements:

    SELECT aa.*
FROM   aa, json_each(aa.b) elem
GROUP  BY aa.a   -- possible, because it's the PK!
HAVING count(*) = 3;

    SQL Fiddle.