Postgres: count rows in jsonb for a specific key

Question

I have a table with two labels: id INT and value JSONB. In value I have a json object props with keys id_1, id_2, and so on, with their respective values.

Is there a way to count the rows where the JSON object props has a specific key, such as id_1?

In this example, there should be two results: rows 1 and 4.

id   |   value
 1   |   {"name": "Jhon",  "props": {"id_1": {"role": "role1", "class": "class1"}, "id_2": {"role": "role2", "class": "class2"}}}
 2   |   {"name": "Frank", "role": ["role1", "role2"]}
 3   |   {"name": "Bob",  "props": {"id_3": {"role": "role3", "class": "class3"}, "id_4": {"role": "role4"}}}
 4   |   {"name": "Carl", "props": {"id_5": {"role": "role5", "class": "class5"}, "id_1": {"class": "class6"}}}

I tried something like this, but to make it work, I have to also specify the value, but the value could change for every row. For example, with this query, I only get one row back.

SELECT count(value)
FROM "myTable" 
where value->'props' ->> 'id_1' = '{"role": "role1", "class": "class1"}'

Agnij · Accepted Answer · 2021-09-27 13:10:00Z

1

Try this-

SELECT COUNT(z.*) FROM (    
SELECT id, value->'props'->>'id_1' as val FROM "myTable" ) z WHERE z.val
IS NOT NULL

answered Sep 27, 2021 at 13:10

Agnij

5813 silver badges14 bronze badges

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

3 Comments

Brododipollo Over a year ago

it counts all the rows :(

Agnij Over a year ago

Even after adding the WHERE clause?

Agnij Over a year ago

@Brododipollo It will work fine after adding WHERE z.val IS NOT NULL

Bergi · Accepted Answer · 2022-05-26 17:00:20Z

0

Use the ? operator to test whether a key exists, regardless of value.

SELECT count(*)
FROM "myTable" 
where value -> 'props' ? 'id_1

answered May 26, 2022 at 17:00

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Postgres: count rows in jsonb for a specific key

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related