Query Postgres for number of items in JSON

Question

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.

Define "number of items" more precisely, please. Attributes in the root node? Do nested elements count? — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 9, 2014 at 15:45
Yes, attributes in the root node is what I meant. I'll update the question. — Manuel Meurer
– Manuel Meurer, Commented Dec 10, 2014 at 6:40

Erwin Brandstetter · Accepted Answer · 2014-12-09 15:52:51Z

5

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.

answered Dec 9, 2014 at 15:52

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query Postgres for number of items in JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related