Postgres 16

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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.

ERROR:  syntax error at or near "I"
LINE 1: I want records that all of its values are string zero ('0')....
        ^

create table mytabel(myfield)as values
 ('{"a": "0", "b": "0"}'::jsonb)
,('{"c": "1", "d": "0"}')
,('{"e": null, "f": true}')
,('{"g": 0, "h": 0.0}')
;

SELECT 4

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

ERROR:  syntax error at or near "0"
LINE 1: ...E NOT jsonb_exists("myfield", '$$ WHERE value != \'0\' $$');
                                                              ^

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