Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_table(
  id int generated by default as identity primary key, 
  json_col json);
insert into my_table (json_col) values
('{    
       "a": {
            "x": {"name": "ben", "success": true},
            "y": {"name": "sarah", "success": true},
            "z": {"name": "john", "success": false}
        }
    }'),
('{
        "a": {
            "m": {"name": "issac", "success": true},
            "n": {"name": "jack", "success": true}
        }
    }')
returning *;

CREATE TABLE




id
json_col


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

2
{
        "a": {
            "m": {"name": "issac", "success": true},
            "n": {"name": "jack", "success": true}
        }
    }



INSERT 0 2


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}
        }
    }



SELECT 1


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

ALTER TABLE



CREATE INDEX




id
json_col


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



SELECT 1


--all names from the row that wasn't filtered out, 
--each name in a separate row with the id of the row it came from
SELECT id,jsonb_path_query(json_col,'$.a.*.name')
FROM my_table
WHERE json_col @@ '$.a.*.success<>true';


id
jsonb_path_query


1
"ben"

1
"sarah"

1
"john"



SELECT 3


--all names from the row that wasn't filtered out, 
--all in an array in a single row marked with the id they came from
SELECT id,jsonb_path_query_array(json_col,'$.a.*.name')
FROM my_table
WHERE json_col @@ '$.a.*.success<>true';


id
jsonb_path_query_array


1
["ben", "sarah", "john"]



SELECT 1


--only the names that had `.success==not true`
--each name in a separate row with the id of the row it came from
SELECT id,jsonb_path_query(json_col,'$.a.*?(@.success<>true).name')
FROM my_table
WHERE json_col @@ '$.a.*.success<>true';


id
jsonb_path_query


1
"john"



SELECT 1


--only the names that had `.success==not true`
--each name in a separate row with the id of the row it came from
SELECT id,jsonb_path_query_array(json_col,'$.a.*?(@.success<>true).name')
FROM my_table
WHERE json_col @@ '$.a.*.success<>true';


id
jsonb_path_query_array


1
["john"]



SELECT 1
id	json_col
1	{ "a": { "x": {"name": "ben", "success": true}, "y": {"name": "sarah", "success": true}, "z": {"name": "john", "success": false} } }
2	{ "a": { "m": {"name": "issac", "success": true}, "n": {"name": "jack", "success": true} } }