Re: GSoC 2015: Extra Jsonb functionality - Mailing list pgsql-hackers

On 19 March 2015 at 13:23, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
> Synopsis: Althrough Jsonb was introduced in PostgreSQL 9.4, there are
> several functions, that still missing. Partially this missing functionality
> was implemented in this extension [1] and the corresponding patch [2]. The
> purpose of this work is to implement the rest of functions accordingly to
> importance.
>
> Benefits: New functionality, than can made the usage of the jsonb more
> convenient.
>
> Deliverables: Implementation of the following functions (in the form of an
> extension
>     * jsonb_delete_jsonb - delete key/value pairs based on the other jsonb.
>       Example of usage:
>
>         =# jsonb_delete_jsonb('{"a": 1, "b": {"c": 2, "d": 3}, "f": [4,
> 5]}'::jsonb, '{"a": 4, "f": [4, 5], "c": 2}'::jsonb);
>
>                  jsonb_delete_jsonb
>         ---------------------------------------
>              {"a": 1, "b": {"c": 2, "d": 3}}

Perhaps it's my misunderstanding, but this would seem to be more of an
intersection operation on keys rather than a delete.

>     * jsonb_slice - extract a subset of an jsonb
>       Example of usage:
>
>         =# jsonb_slice('{"a": 1, "b": {"c": 2}, "d": {"f": 3}}'::jsonb,
> ARRAY['b', 'f', 'x']);
>
>                jsonb_slice
>         ---------------------------
>           {"b": {"c": 2}, "f": 3}
>
>     * jsonb_to_array - get jsonb keys and values as an array
>       Example of usage:
>
>         =# jsonb_to_array('{"a": 1, "b": {"c": 2}, "d": [3, 4]}'::jsonb);
>
>                 jsonb_to_array
>         ------------------------------
>            {a, 1, b, c, 2, d, 3, 4}

Is there a use-case for the example you've given above, where you take
JSON containing objects and arrays, and flatten them out into a
one-dimensional array?

>
>     * jsonb_keys - get jsonb keys as an array
>       Example of usage:
>
>         =# jsonb_keys('{"a": 1, "b": {"c": 2}}'::jsonb);
>
>             jsonb_keys
>         -----------------
>             {a, b, c}
>
>     * jsonb_vals - get jsonb values as an array
>       Example of usage:
>
>         =# jsonb_vals('{"a": 1, "b": {"c": 2}, "d": [3, 4]}'::jsonb);
>
>             jsonb_vals
>         ------------------
>            {1, 2, 3, 4}
>
>     * jsonb_add_to_path - append a new element to jsonb value at the
> specific path
>       Example of usage:
>
>        =# jsonb_add_to_path('{"a": 1, "b": {"c": ["d", "f"]}}'::jsonb, {b,
> c}::text[], '["g"]'::jsonb);
>
>                    jsonb_add_to_path
>         -------------------------------------------
>            {"a": 1, "b": {"c": ["d", "f", "g"]}}

What should happen if "g" or {"g"} were used instead?

>     * jsonb_intersection - extract intersecting key/value pairs
>       Example of usage:
>
>         =# jsonb_intersection('{"a": 1, "b": 2, "d": {"f": 3}, "g": [4,
> 5]}'::jsonb, '{"b": 2, "c": 3, "f": 3, "g": [4, 5]}'::jsonb);
>
>              jsonb_intersection
>         ----------------------------
>             {"b": 2, "g": [4, 5]}

Could there be a corresponding jsonb_except function which does the
opposite (i.e. returns everything on the left side except where it
matches with the right)?

Thanks.

-- 
Thom