Re: GSoC 2015: Extra Jsonb functionality

On 19 March 2015 at 13:23, Dmitry Dolgov <9erthalion6(at)gmail(dot)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