Postgres: count rows in jsonb for a specific key-CodePudding

I have a table with two labels: "id" (int) and "value"(jsonb). In "value" column i have another json object ("props") with keys id_1, id_2 etc. and their rispectivly values.

Is there a way to count the rows where the json object "props" has a specific key? For exemple "id_1"? In this example the resul shoud be 2 (row 1 row 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 specify the value too and not only the key. And the value could change for every row. In Fact the result is 1 (wrong because it have to be 2)

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

CodePudding user response：

Try this-

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