Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/*
I want records that all of its values are string zero ('0'). 
number & name of keys are not fixed and varies between records. 
I've tried this and it does not work

this query also work as expected but it need inner join, 
I am wondering if we could avoid that.
*/
create table mytabel(myfield)as values
 ('{"a": "0", "b": "0"}'::jsonb)
,('{"c": "1", "d": "0"}')
,('{"e": null, "f": true}')
,('{"g": 0, "h": 0.0}')
,('{"i": "0", "j": false}')
;

--OP
SELECT * FROM mytabel 
  WHERE NOT jsonb_exists("myfield", '$$ WHERE value != ''0'' $$');

SELECT 5

myfield

{"a": "0", "b": "0"}

{"c": "1", "d": "0"}

{"e": null, "f": true}

{"g": 0, "h": 0.0}

{"i": "0", "j": false}

SELECT 5

--OP
SELECT *
FROM mytabel WHERE 
NOT EXISTS (
    SELECT 1
    FROM jsonb_each_text(myfield) AS kv
    WHERE kv.value <> '0'
);

myfield

{"a": "0", "b": "0"}

SELECT 1

SELECT *
FROM mytabel WHERE not myfield @? '$.* ? (@ != "0" || @.type()<>"string")';

myfield

{"a": "0", "b": "0"}

SELECT 1

SELECT *
FROM mytabel WHERE not myfield @@ '$.* <> "0" || $.*.type()<>"string"';

myfield

{"a": "0", "b": "0"}

SELECT 1

SELECT *
FROM mytabel WHERE myfield @@ '!exists($.*?(@<>"0" || @.type()<>"string"))';

myfield

{"a": "0", "b": "0"}

SELECT 1

select setseed(.42);

insert into mytabel
select jsonb_build_object(('{a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r}'::text[])[(1+random()*17)::int],
                          case (random()*6)::int 
                          when 0 then to_jsonb(0::int) 
                          when 1 then to_jsonb('abc'::text)
                          when 2 then to_jsonb(0.0::float)
                          when 3 then to_jsonb(true::boolean)
                          when 4 then to_jsonb(null::jsonb)
                          when 5 then to_jsonb('2024-02-02'::date)
                          when 6 then to_jsonb('0'::text)
                          end,
                          ('{a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r}'::text[])[(1+random()*17)::int],
                          case (random()*6)::int 
                          when 0 then to_jsonb(0::int) 
                          when 1 then to_jsonb('abc'::text)
                          when 2 then to_jsonb(0.0::float)
                          when 3 then to_jsonb(true::boolean)
                          when 4 then to_jsonb(null::jsonb)
                          when 5 then to_jsonb('2024-02-02'::date)
                          when 6 then to_jsonb('0'::text)
                          end,
                          ('{a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r}'::text[])[(1+random()*17)::int],
                          case (random()*6)::int 
                          when 0 then to_jsonb(0::int) 
                          when 1 then to_jsonb('abc'::text)
                          when 2 then to_jsonb(0.0::float)
                          when 3 then to_jsonb(true::boolean)
                          when 4 then to_jsonb(null::jsonb)
                          when 5 then to_jsonb('2024-02-02'::date)
                          when 6 then to_jsonb('0'::text)
                          end)
from generate_series(1,1e5);

select jsonb_pretty(myfield) 
from mytabel tablesample bernoulli(.42)repeatable(.42)
limit 5;

create index on mytabel using gin(myfield jsonb_path_ops);

setseed

SELECT 1

INSERT 0 100000

jsonb_pretty

{
    "e": true,
    "l": null,
    "m": "2024-02-02"
}

{
    "b": "abc",
    "i": "0"
}

{
    "i": "abc",
    "j": "0",
    "q": "2024-02-02"
}

{
    "g": null,
    "i": 0,
    "k": null
}

{
    "g": 0,
    "n": "abc",
    "o": "0"
}

SELECT 5

CREATE INDEX

vacuum analyze mytabel;

VACUUM

explain analyze verbose SELECT *
FROM mytabel WHERE myfield @@ '!exists($.*?(@<>"0" || @.type()<>"string"))';

QUERY PLAN

Bitmap Heap Scan on public.mytabel  (cost=768.08..805.05 rows=10 width=42) (actual time=18.512..125.622 rows=192 loops=1)

  Output: myfield

  Recheck Cond: (mytabel.myfield @@ '!(exists ($.*?(@ != "0" || @.type() != "string")))'::jsonpath)

  Rows Removed by Index Recheck: 99813

  Heap Blocks: exact=907

  ->  Bitmap Index Scan on mytabel_myfield_idx  (cost=0.00..768.07 rows=10 width=0) (actual time=18.386..18.386 rows=100005 loops=1)

        Index Cond: (mytabel.myfield @@ '!(exists ($.*?(@ != "0" || @.type() != "string")))'::jsonpath)

Planning Time: 0.320 ms

Execution Time: 125.705 ms

EXPLAIN