Search code examples
postgresqljsonb

query records that all values of jsonb filed are zero

I have a table with JSONB field like this:

{'a': '0', 'b': '0'}

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

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

this query also work as expected but it need inner join, I am wondering if we could avoid that.

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

  • You can use @@ JSONPath predicate check operator:

    jsonb @@ jsonpath → boolean
    Returns the result of a JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then NULL is returned.
    '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t

    Demo at db<>fiddle:

    SELECT *
FROM mytabel WHERE not myfield @@ '$.* <> "0" || $.*.type()<>"string"';
    myfield
    {"a": "0", "b": "0"}

    In your example you tried to use jsonb_exists(), which is the function behind the ? operator:

    jsonb ? text → boolean
    Does the text string exist as a top-level key or array element within the JSON value?
    '{"a":1, "b":2}'::jsonb ? 'b' → t

    Your use of the function results in a search for presence of the key "$$ WHERE value != \'0\' $$'" - that text does not work as any sort of a WHERE clause.