How to check if a value exists in any key of a json object?

Question

I have a jsonb column it has many keys. If any key has a particular value which I am looking for it should return true.

As keys doen't matter for my requirement, my idea is to check against an array of extracted values. So is there a way to get all the values of a jsonb into an array?

if json_array_length(tms_hlpr_usr_has_authority_fr_srvc_requests(usr_id_,org_id_)) > 0 then
        _extra_where = _extra_where || 
            ' and ' || quote_literal(usr_id_) || '  =  any(srvc_req.form_data->>[how to check all keys here]) and srvc_req.is_deleted is not true ';
end if;

Please **[edit]** your question (by clicking on the [edit] link below it) and add some sample data and the expected output based on that data as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking text tables. ([edit] your question - do **not** put code or additional information in comments) — , Dec 26 '22 at 08:49

Erwin Brandstetter · Answer 1 · 2022-12-26T09:51:47.163

Assuming you are building an SQL command to dynamically execute with EXECUTE in a Postgres function.

To check whether any top-level key has the given value, the most elegant way will be an SQL/JSON path expression. This can be supported with an index and should be decently fast. No need to expensively extract all values into an array first.

Basic example to check for the value 2:

SELECT jsonb_path_exists(jsonb '{"a":1, "b":2}', '$.* ? (@ == 2)');

To pass in your variable usr_id_ of unknown type (in place of the numeric value 2 of the basic example):

SELECT jsonb_path_exists(jsonb '{"a":1, "b":2}'
                       , '$.* ? (@ == $usr_id)'
                       , jsonb_build_object('usr_id', 1));

Concatenating the WHERE clause in your PL/pgSQL code block can look like this:

IF json_array_length(tms_hlpr_usr_has_authority_fr_srvc_requests(usr_id_,org_id_)) > 0 THEN
   _extra_where = _extra_where
           || $j$ AND jsonb_path_exists(srvc_req.form_data, '$.* ? (@ == $usr_id)', $1) AND srvc_req.is_deleted IS NOT TRUE $j$;
END IF;

Using dollar-quoting to allow for nested single quotes. See:

Insert text with single quotes in PostgreSQL

Supply the value for the parameter symbol $1 in a USING clause to the EXECUTE command. Like:

EXECUTE 'SELECT * FROM srvc_req ' || _extra_where
USING   jsonb_build_object('usr_id', usr_id_);  -- $1 !

Returning JSON arrary with particular property using Postgres

(This would be much easier to demonstrate if you had provided your actual minimal function.)

How to check if a value exists in any key of a json object?

1 Answers1