By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
WITH mytable AS (
SELECT '{"data":{"reportData": {"AAA" : {"count" : 100}, "BBB" : {"count" : 200}}}}'::jsonb as mydata
)
SELECT
jsonb_path_query(mydata, '$.**.count')
FROM mytable
| jsonb_path_query |
|---|
| 100 |
| 200 |
SELECT 2
WITH mytable AS (
SELECT '{"data":{"reportData": {"AAA" : {"count" : 100}, "BBB" : {"count" : 200}}}}'::jsonb as mydata
)
SELECT
users.value -> 'count'
FROM
mytable,
jsonb_each(mydata -> 'data' -> 'reportData') as users
| ?column? |
|---|
| 100 |
| 200 |
SELECT 2