Use variables as search keys within jsonb_extract_path

Question

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.

klin · Accepted Answer · 2019-10-07 13:47:05Z

3

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.

edited Oct 7, 2019 at 13:47

answered Oct 6, 2019 at 0:57

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C.Acarbay Over a year ago

Nice, now is there any way I can also have a wildcard key?

C.Acarbay Over a year ago

Thank you this helps a lot. Although I'm not sure if we could bump up to Postgres12.

Collectives™ on Stack Overflow

Use variables as search keys within jsonb_extract_path

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related