Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE applications
(  id          INT generated by default as identity PRIMARY KEY,
   application JSONB
);

select setseed(.42);--stabilise random between re-runs
--randomised entries
INSERT INTO applications(application)
SELECT jsonb_build_object(
            'type_code',(1+random()*9)::int,
            'persons', jsonb_build_array(
                            jsonb_build_object(
                                'type_code',(1+random()*9)::int,
                                'firstname',case when .5>random()
                                                 then 'John'
                                                 else gen_random_uuid()::text
                                            end,
                                'lastname',case when .5>random()
                                                 then 'Doe'
                                                 else gen_random_uuid()::text
                                            end
                             )
                        )
        )
FROM generate_series(2, 2e5);

CREATE INDEX gin_idx ON applications 
  USING gin (application jsonb_path_ops);

--sampling
select id,jsonb_pretty(application) from applications 
tablesample bernoulli(.42)repeatable(.42)
limit 3;

CREATE TABLE




setseed






SELECT 1



ERROR:  could not extend file "base/5/16385": No space left on device
HINT:  Check free disk space.


vacuum analyze applications;

ERROR:  relation "applications" does not exist


--Then I try to select the data like the following:

EXPLAIN ANALYZE
SELECT * FROM applications
WHERE applications.application @? '$.persons[*] ? (@.type_code == 3)';

ERROR:  relation "applications" does not exist
LINE 4: SELECT * FROM applications
                      ^


EXPLAIN ANALYZE
SELECT * FROM applications
WHERE jsonb_path_exists(
      applications.application,
      '$.persons[*] ? (@.type_code == 3)'
);

ERROR:  relation "applications" does not exist
LINE 2: SELECT * FROM applications
                      ^


CREATE OR REPLACE FUNCTION my_jsonb_path_exists(arg1 jsonb,arg2 jsonpath)
RETURNS boolean AS 'SELECT $1 @? $2' LANGUAGE 'sql' IMMUTABLE;

EXPLAIN ANALYZE
SELECT * FROM applications
WHERE my_jsonb_path_exists(
      applications.application,
      '$.persons[*] ? (@.type_code == 3)'
);

CREATE FUNCTION



ERROR:  relation "applications" does not exist
LINE 5: SELECT * FROM applications
                      ^