CODEWITHSUNDEEP
postgresqlplpgsqljsonb
C.Acarbay
C.Acarbay

Reputation: 434

Use variables as search keys within jsonb_extract_path

I'm working on an event that compares the jsonb values within a column between the last entry and the new entry. I have another table that defines what values to compare by passing on a jsonb object itself. What I'm working with goes something like this:

select jsonb_extract_path(_tmp_jsonb, 'key_1', 'key2') into target;

and the jsonb object I'm looking inside is this:

{
 "key1": {"key2": 15},
 "key3": {"key2": {"key4": 25}}
}

Now I can get 15 with the above command no problem, but what I'd like to do is be able pass any key combinations as a jsonb array like {"search_keys":["key3", "key2", "key4"]}. So something like this:

select jsonb_extract_path(_tmp_jsonb, ["key3", "key2", "key4"]) into target;

More clearly, what I'm asking is how to use variable length argument arrays in postgres, as you would do in python with *args.

Upvotes: 2

Views: 1219

Answers (1)

klin
klin

Reputation: 121574

Use the #> operator instead of the function. The right operand is a text array.

declare
    _tmp_jsonb jsonb;
    _path text[];
    target jsonb;
begin
    _tmp_jsonb := '{"key1": {"key2": 15}, "key3": {"key2": {"key4": 25}}}';
    _path := array['key3', 'key2', 'key4'];
    target := _tmp_jsonb #> _path;
    ...

By the way, do not use select for simple assignments, it is too costly.

In Postgres 12 you can use SQL/JSON path functions, e.g.:

declare
    _tmp_jsonb jsonb;
    _path jsonpath; -- !!
    target jsonb;
begin
    _tmp_jsonb := '{"key1": {"key2": 15}, "key3": {"key2": {"key4": 25}}}';
    _path := '$.key3.key2.key4';
    target := jsonb_path_query(_tmp_jsonb, _path);
    ...

The new feature is flexible and powerful as a json path may contain wildcards and supports recursion.

Read in the documentation:

See also jsonpath examples in this answer.

Upvotes: 3

Related Questions