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

Zegarek · Accepted Answer · 2024-01-16 11:48:54Z

3

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';

edited Jan 16, 2024 at 11:48

answered Jan 7, 2024 at 16:49

Zegarek

29.8k5 gold badges27 silver badges32 bronze badges

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

7 Comments

maxwell_rob33 Over a year ago

perfect! Thank you so much! also will take your upgrade suggestion into account

maxwell_rob33 Over a year ago

can help me with getting the value as a select as well please?

Zegarek Over a year ago

@maxwell_rob33 What do you mean?

maxwell_rob33 Over a year ago

in my example - I'd like to select the names of the people which passed the filter, ie "issac" & "jack"

Zegarek Over a year ago

@maxwell_rob33 Are you sure about issac and jack? My understanding was that in your example they are supposed to be filtered out, discarded: "first row will be selected, second row will be filtered". Assuming you want to filter out the second row and get the names from the first one (that does match the "any of their a.<something>.success is not true" criterion), do you want all of the names present in the json that matched/wasn't filtered out, or only the name that made it match (having ."success"==not true)? In this case that would be john. Demo

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related