searching in multiple random named sub keys in json object in postgres

Question

assuming I have json column which look something like:

-- first row:
{
        "a": {
            "x": {"name": "ben", "success": true},
            "y": {"name": "sarah", "success": true},
            "z": {"name": "john", "success": false}
        }
    }

-- second row:
{
        "a": {
            "m": {"name": "issac", "success": true},
            "n": {"name": "jack", "success": true},
        }
    }

I want to select all rows which any of their a.<something>.success is not true.

in my Example - first row will be selected, second row will be filtered.

As you can see all jsons start with common key a, but under it there are unknown number of "childs" with unknown names ('x', 'y', 'z', 'm', 'n'). under each child with unknown name - there is a common known key I'd like to filter by: success.

Question is how Can filter such rows?

I got to something I could not complete:

SELECT * FROM my_table WHERE json_col::json -> 'a' -> <don't know what to put here> -> 'success' = true

Answer 1

You can explode the json value with json_each() into key:value pairs and check the values. That will work in an exists condition: demo

SELECT * 
FROM my_table
WHERE exists (select from json_each(json_col->'a')_(key,value)
              where (value->>'success') <> 'true');

id	json_col
1	{        "a": {             "x": {"name": "ben", "success": true},             "y": {"name": "sarah", "success": true},             "z": {"name": "john", "success": false}         }     }

If you switch from json to jsonb you'll be able to speed up the search with a GIN index, get rid of insignificant whitespace, deduplicate and order your keys, get more functions and operators to choose from, like @@ (that, and other jsonpath-related features are supported from version 12 onwards):

ALTER TABLE my_table ALTER COLUMN json_col TYPE jsonb;
CREATE INDEX ON my_table USING GIN(json_col jsonb_path_ops);

SELECT * 
FROM my_table
WHERE json_col @@ '$.a.*.success<>true';

If you're on version 11, its support ended on 2023-11-09. Consider upgrading.

---

To get the name(s) that had .success<>true, you can use jsonb_path_query():

SELECT id,jsonb_path_query(json_col,'$.a.*?(@.success<>true).name')
FROM my_table
WHERE json_col @@ '$.a.*.success<>true';