Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table "myTable"(id int generated always as identity primary key,
                       "myColumn" jsonb);
insert into "myTable"("myColumn") values
('{"foo": {"key0": {"bar": "myValueA"},
           "key1": {"bar": "myValueB"},
           "keyN": {"bar": "myValueN"}
          }
}'),
('{"foo": {"key123": {"bar": "myValueC"},
           "key345": {"bar": "myValueD"}
          }
}'),
('{"foo": {"XYZ": {"bar": "myValueN"}
          }
}')
returning id,jsonb_pretty("myColumn");

CREATE TABLE




id
jsonb_pretty


1
{
    "foo": {
        "key0": {
            "bar": "myValueA"
        },
        "key1": {
            "bar": "myValueB"
        },
        "keyN": {
            "bar": "myValueN"
        }
    }
}

2
{
    "foo": {
        "key123": {
            "bar": "myValueC"
        },
        "key345": {
            "bar": "myValueD"
        }
    }
}

3
{
    "foo": {
        "XYZ": {
            "bar": "myValueN"
        }
    }
}



INSERT 0 3


/*
I would like to get all the records that have 
the text "myValueX" among all the paths 'foo' > ANY_KEY > 'bar'

It could be 'foo' > 'key0' > 'bar' or 'foo' > 'key1' > 'bar' or any keyN.

In "pseudo" PostgreSQL:

SELECT * FROM "myTable" WHERE "myColumn" #>> '{foo,*,bar}' = 'myValue';
where * would mean "can be any key".
*/
select * from "myTable" 
where "myColumn" @@ '$.foo.*.bar=="myValueN"';


id
myColumn


1
{"foo": {"key0": {"bar": "myValueA"}, "key1": {"bar": "myValueB"}, "keyN": {"bar": "myValueN"}}}

3
{"foo": {"XYZ": {"bar": "myValueN"}}}



SELECT 2


--adding samples to show performance with index support
select setseed(.42);

insert into "myTable"("myColumn") select
jsonb_build_object('foo',
                   jsonb_agg(n2.obj)
                  )
from generate_series(1,7e4)_(n), 
lateral(select n,  jsonb_build_object(case when random()>.2
                                           then left(md5(n::text),(3+random()*7)::int)
                                           else 'key'||(random()*7)::int end,
                                      jsonb_build_object('bar',
                                                         'myValue'||chr(65+(random()*25)::int))
                                     ) as obj
   from generate_series(n,(n+random()*7)::int))n2
group by _.n;

create index on "myTable" using gin("myColumn");

select * from "myTable" tablesample bernoulli(.42)repeatable(.42) limit 10;


setseed






SELECT 1



INSERT 0 70000



CREATE INDEX




id
myColumn


232
{"foo": [{"d18f655c3": {"bar": "myValueF"}}, {"d18f655c3": {"bar": "myValueB"}}, {"d18f65": {"bar": "myValueG"}}, {"key0": {"bar": "myValueJ"}}]}

458
{"foo": [{"8fecb20": {"bar": "myValueX"}}, {"8fec": {"bar": "myValueY"}}, {"8fecb": {"bar": "myValueD"}}, {"8fec": {"bar": "myValueX"}}, {"key1": {"bar": "myValueR"}}]}

541
{"foo": [{"04e": {"bar": "myValueC"}}, {"04ecb1f": {"bar": "myValueS"}}, {"04ecb1fa2": {"bar": "myValueV"}}, {"key1": {"bar": "myValueE"}}, {"04ecb1f": {"bar": "myValueO"}}, {"04ecb1fa": {"bar": "myValueP"}}]}

1047
{"foo": [{"b166b57d1": {"bar": "myValueI"}}, {"b166b": {"bar": "myValueH"}}, {"b166b57": {"bar": "myValueG"}}]}

1439
{"foo": [{"3df07f": {"bar": "myValueO"}}, {"3df0": {"bar": "myValueX"}}]}

1840
{"foo": [{"2f63": {"bar": "myValueL"}}, {"2f635a9f": {"bar": "myValueK"}}, {"2f635a9fe": {"bar": "myValueF"}}, {"2f635": {"bar": "myValueJ"}}, {"2f6": {"bar": "myValueQ"}}]}

2377
{"foo": [{"f76cb028": {"bar": "myValueP"}}, {"key3": {"bar": "myValueW"}}, {"f76cb028c": {"bar": "myValueR"}}, {"f76cb0": {"bar": "myValueP"}}, {"f76cb028c": {"bar": "myValueU"}}]}

2684
{"foo": [{"1f26": {"bar": "myValueR"}}, {"1f2627": {"bar": "myValueJ"}}]}

3002
{"foo": [{"e875ef": {"bar": "myValueF"}}, {"key5": {"bar": "myValueD"}}, {"e875effed": {"bar": "myValueQ"}}, {"e875eff": {"bar": "myValueI"}}, {"e875": {"bar": "myValueV"}}]}

3434
{"foo": [{"key2": {"bar": "myValueP"}}, {"bd11474c79": {"bar": "myValueF"}}, {"key3": {"bar": "myValueW"}}, {"bd11474c79": {"bar": "myValueN"}}, {"bd11": {"bar": "myValueO"}}]}



SELECT 10


vacuum analyze "myTable";

VACUUM


explain analyze verbose
select * from "myTable" 
where "myColumn" @@ '$.foo.*.bar=="myValueN"';


QUERY PLAN


Bitmap Heap Scan on public."myTable"  (cost=150.48..2653.34 rows=14069 width=238) (actual time=3.226..24.910 rows=11649 loops=1)

  Output: id, "myColumn"

  Recheck Cond: ("myTable"."myColumn" @@ '($."foo".*."bar" == "myValueN")'::jsonpath)

  Heap Blocks: exact=2318

  ->  Bitmap Index Scan on "myTable_myColumn_idx"  (cost=0.00..146.96 rows=14069 width=0) (actual time=2.918..2.918 rows=11649 loops=1)

        Index Cond: ("myTable"."myColumn" @@ '($."foo".*."bar" == "myValueN")'::jsonpath)

Planning Time: 0.392 ms

Execution Time: 25.548 ms



EXPLAIN

id	jsonb_pretty
1	{ "foo": { "key0": { "bar": "myValueA" }, "key1": { "bar": "myValueB" }, "keyN": { "bar": "myValueN" } } }
2	{ "foo": { "key123": { "bar": "myValueC" }, "key345": { "bar": "myValueD" } } }
3	{ "foo": { "XYZ": { "bar": "myValueN" } } }

id	myColumn
1	{"foo": {"key0": {"bar": "myValueA"}, "key1": {"bar": "myValueB"}, "keyN": {"bar": "myValueN"}}}
3	{"foo": {"XYZ": {"bar": "myValueN"}}}

id	myColumn
232	{"foo": [{"d18f655c3": {"bar": "myValueF"}}, {"d18f655c3": {"bar": "myValueB"}}, {"d18f65": {"bar": "myValueG"}}, {"key0": {"bar": "myValueJ"}}]}
458	{"foo": [{"8fecb20": {"bar": "myValueX"}}, {"8fec": {"bar": "myValueY"}}, {"8fecb": {"bar": "myValueD"}}, {"8fec": {"bar": "myValueX"}}, {"key1": {"bar": "myValueR"}}]}
541	{"foo": [{"04e": {"bar": "myValueC"}}, {"04ecb1f": {"bar": "myValueS"}}, {"04ecb1fa2": {"bar": "myValueV"}}, {"key1": {"bar": "myValueE"}}, {"04ecb1f": {"bar": "myValueO"}}, {"04ecb1fa": {"bar": "myValueP"}}]}
1047	{"foo": [{"b166b57d1": {"bar": "myValueI"}}, {"b166b": {"bar": "myValueH"}}, {"b166b57": {"bar": "myValueG"}}]}
1439	{"foo": [{"3df07f": {"bar": "myValueO"}}, {"3df0": {"bar": "myValueX"}}]}
1840	{"foo": [{"2f63": {"bar": "myValueL"}}, {"2f635a9f": {"bar": "myValueK"}}, {"2f635a9fe": {"bar": "myValueF"}}, {"2f635": {"bar": "myValueJ"}}, {"2f6": {"bar": "myValueQ"}}]}
2377	{"foo": [{"f76cb028": {"bar": "myValueP"}}, {"key3": {"bar": "myValueW"}}, {"f76cb028c": {"bar": "myValueR"}}, {"f76cb0": {"bar": "myValueP"}}, {"f76cb028c": {"bar": "myValueU"}}]}
2684	{"foo": [{"1f26": {"bar": "myValueR"}}, {"1f2627": {"bar": "myValueJ"}}]}
3002	{"foo": [{"e875ef": {"bar": "myValueF"}}, {"key5": {"bar": "myValueD"}}, {"e875effed": {"bar": "myValueQ"}}, {"e875eff": {"bar": "myValueI"}}, {"e875": {"bar": "myValueV"}}]}
3434	{"foo": [{"key2": {"bar": "myValueP"}}, {"bd11474c79": {"bar": "myValueF"}}, {"key3": {"bar": "myValueW"}}, {"bd11474c79": {"bar": "myValueN"}}, {"bd11": {"bar": "myValueO"}}]}