Обсуждение: GSoC 2015: Extra Jsonb functionality

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

Dmitry Dolgov wrote:

>     * 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}

This is a bit strange.  Why did "f" get flattened out of "d"?  Is the
resulting document still valid for the purposes of an application using
it?  I think I'd expect the result to be {"b": {"c": 2}, "d": {"f": 3}}

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 19 March 2015 at 14:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Dmitry Dolgov wrote:
>
>>     * 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}
>
> This is a bit strange.  Why did "f" get flattened out of "d"?  Is the
> resulting document still valid for the purposes of an application using
> it?  I think I'd expect the result to be {"b": {"c": 2}, "d": {"f": 3}}

Why would "d" be output when it wasn't in the requested slice?
Although I'm still a bit confused about "f" being produced.

-- 
Thom

Thom Brown wrote:
> On 19 March 2015 at 14:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > Dmitry Dolgov wrote:
> >
> >>     * 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}
> >
> > This is a bit strange.  Why did "f" get flattened out of "d"?  Is the
> > resulting document still valid for the purposes of an application using
> > it?  I think I'd expect the result to be {"b": {"c": 2}, "d": {"f": 3}}
> 
> Why would "d" be output when it wasn't in the requested slice?

Because it contains "f".

> Although I'm still a bit confused about "f" being produced.

I guess you could say that the second argument is an array of element
paths, not key names.  So to get the result I suggest, you would have to
use ARRAY['{b}', '{d,f}', '{x}'].  (Hm, this is a non-rectangular
array actually... I guess I'd go for ARRAY['b', 'd//f', 'x'] instead, or
whatever the convention is to specify a json path).

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 19 March 2015 at 14:35, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Thom Brown wrote:
>> On 19 March 2015 at 14:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> > Dmitry Dolgov wrote:
>> >
>> >>     * 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}
>> >
>> > This is a bit strange.  Why did "f" get flattened out of "d"?  Is the
>> > resulting document still valid for the purposes of an application using
>> > it?  I think I'd expect the result to be {"b": {"c": 2}, "d": {"f": 3}}
>>
>> Why would "d" be output when it wasn't in the requested slice?
>
> Because it contains "f".

Okay, so it pulls it all parents?  So I guess you'd get this too:

SELECT jsonb_slice('{"a": 1, "b": {"c": 2}, "d": {"f": 3}, "f":
4}'::jsonb, ARRAY['b', 'f', 'x']);
                 jsonb_slice
------------------------------------------------{"a": 1, "b": {"c": 2}, "d": {"f": 3}, "f": 4}

>> Although I'm still a bit confused about "f" being produced.
>
> I guess you could say that the second argument is an array of element
> paths, not key names.  So to get the result I suggest, you would have to
> use ARRAY['{b}', '{d,f}', '{x}'].  (Hm, this is a non-rectangular
> array actually... I guess I'd go for ARRAY['b', 'd//f', 'x'] instead, or
> whatever the convention is to specify a json path).

I think that's where jsquery would come in handy.
-- 
Thom

Thom Brown wrote:
> On 19 March 2015 at 14:35, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > Thom Brown wrote:
> >> On 19 March 2015 at 14:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >> > Dmitry Dolgov wrote:
> >> >
> >> >>     * jsonb_slice - extract a subset of an jsonb
> >> >>       Example of usage:

> Okay, so it pulls it all parents?  So I guess you'd get this too:
> 
> SELECT jsonb_slice('{"a": 1, "b": {"c": 2}, "d": {"f": 3}, "f":
> 4}'::jsonb, ARRAY['b', 'f', 'x']);
> 
>                   jsonb_slice
> ------------------------------------------------
>  {"a": 1, "b": {"c": 2}, "d": {"f": 3}, "f": 4}

Yeah, except "a" wouldn't be output, of course.  (The example gets more
interesting if "d" contains more members than just "f".  Those would not
get output.)


> >> Although I'm still a bit confused about "f" being produced.
> >
> > I guess you could say that the second argument is an array of element
> > paths, not key names.  So to get the result I suggest, you would have to
> > use ARRAY['{b}', '{d,f}', '{x}'].  (Hm, this is a non-rectangular
> > array actually... I guess I'd go for ARRAY['b', 'd//f', 'x'] instead, or
> > whatever the convention is to specify a json path).
> 
> I think that's where jsquery would come in handy.

If that's what we think, then perhaps we shouldn't accept jsonb_slice at
all because of ambiguous mode of operation.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 20 March 2015 at 11:21, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
>> Perhaph it's my misunderstanding, but this would seem to be more of an
>> intersection operation on keys rather than a delete.
> Hm...why? We remove all elements, which are contains in the first and second
> jsonb ("f": [4, 5] in this case) from the first one.

On further thought, yes, I agree.

>> 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)?
> and if I understand your question correctly, this is exactly what the
> jsonb_delete_jsonb will do, isn't it?.

Ah, yes, that's true.

>> 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?
> Hm...actually I don't know about such use-cases. This function is analog of
> the hstore_to_array (and the similar function hstore_to_matrix), which is
> used sometimes, judging by github. So I thought this function should be
> implemented (after this question I'm not so sure).

Yeah, hstore was just key=>value, so flattening it out resulted in a
simple {key,value,key,value} array.  I don't think that's useful with
json.

>> What should happen if "g" or {"g"} were used instead?
> Did you mean {"g": "key"}? Hmm...but in any case, I suppose this new object
> should be appended to the array as a regular element.
>     =# jsonb_add_to_path('{"b": {"c": ["d", "f"]}}'::jsonb, {b, c}::text[],
> '"g"'::jsonb);
>
>             jsonb_add_to_path
>     ---------------------------------------
>        {"b": {"c": ["d", "f", "g"]}}
>

Would this also be the case for this function?...

# jsonb_add_to_path('{"b": {"c": ["d", "f"]}}'::jsonb, {b, c}::text[],
'{"g":4}'::jsonb);        jsonb_add_to_path
------------------------------------{"b": {"c": ["d", "f", {"g": 4}]}}

-- 
Thom

On 3/19/15 9:07 AM, Thom Brown wrote:
>> >                 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?

There are a lot of things proposed here that are completely ignoring the 
idea of nested elements, which I think is a big mistake.

Frankly, I think the whole proposal needs to be rethought with an eye 
towards supporting and preserving nested elements instead of trying to 
just flatten everything out. If a user wanted things flat they would 
have just started with that in the first place.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 3/21/15 12:49 PM, Dmitry Dolgov wrote:
>  > Frankly, I think the whole proposal needs to be rethought with an eye
> towards supporting and preserving nested elements instead of trying to
> just flatten everything out.
>
> Can you pls show me few examples what do you mean exactly?

All the comments others have made in this thread, for starters.

Your proposal seems intent on completely flattening all JSON documents, 
or treating them as if they were flat. I see zero chance of that being 
accepted.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com