[HACKERS] Proposal about a "deep" versions for some jsonb functions - Mailing list pgsql-hackers
| From | Dmitry Dolgov |
|---|---|
| Subject | [HACKERS] Proposal about a "deep" versions for some jsonb functions |
| Date | |
| Msg-id | CA+q6zcU+gy1+dxQD09MSz8Zwqq+sPPfS-6GYKmyNqGVQDFeQbg@mail.gmail.com Whole thread Raw |
| List | pgsql-hackers |
Hi
As far as I know, since 9.5 we're missing some convenient features, namely a
deepversion of `jsonb_concat` and `jsonb_minus`. There are already few feature
requests about `jsonb_minus` (see [1], [2]) and a lot of confusion and requests
about a deep version of `jsonb_concat`. From my point of view they're pretty
much related, so I want to propose the following description for this
functionality and eventually implement it.
# jsonb_minus
```
jsonb_minus(jsonb, jsonb, deep=False)
```
Looks like we have to abandon "-" operator for that purpose (see a concern
about that in this thread [2]).
In general this functionality is something like the relative complement for two
jsonb objects. Basically we're taking all the paths inside all jsonb objects
and remove duplicated paths from the left one. Of course an actual
implementation may be different, but I think it's a nice way of thinking about
this logic.
Here are few examples, where "->" is an operation to get an actual value,
".->" - an operation to get a next key, "#->" an operation to get a value from
an array ("-" operator is just for the sake of readability):
------------------------------------------------------------------------------
{"a": 1} - {"a": 1}
=> null
paths:
a -> 1
a -> 1
------------------------------------------------------------------------------
{"a": 1} - {"a": 2}
=> {"a": 1}
paths:
a -> 1
a -> 2
------------------------------------------------------------------------------
{"a": 1} - {"a": {"b": 1}}
=> {"a": 1}
paths:
a ->
a -> .b -> 1
------------------------------------------------------------------------------
{"a": 1, "b": {"c": 2}} - {"b": 1, "b": {"c": 3}}
=> {"b": {"c": 2}}
paths:
a -> 1
b .-> c -> 2
b -> 1
b .-> c -> 3
------------------------------------------------------------------------------
{"a": {"b": 1}} - {"a": {"b": 1}}
=> null
paths:
a .-> b -> 1
a .-> b -> 1
------------------------------------------------------------------------------
{"a": {"b": 1, "c": 2}} - {"a": {"b": 1}}
=> {"a": {"b": 1}}
paths:
a .-> b -> 1
a .-> c -> 2
a .-> b -> 1
------------------------------------------------------------------------------
{"a": {
"b": {"b1": 1},
"c": {"c2": 2}
}}
-
{"a": {
"b": {"b1": 1},
"c": {"c2": 3}
}}
=> {"a": {"c": {"c2": 2}}
paths:
a .-> b .-> b1 -> 1
a .-> c .-> c2 -> 2
a .-> b .-> b1 -> 1
a .-> c .-> c2 -> 3
------------------------------------------------------------------------------
{"a": [1, 2, 3]} - {"a": [1, 2]}
=> {"a": [3]}
paths:
a #-> 1
a #-> 2
a #-> 3
a #-> 1
a #-> 2
------------------------------------------------------------------------------
{"a": [{"b": 1}, {"c": 2}]} - {"a": [{"b": 1}, {"c": 3}]}
=> {"a": [{"c": 3}]}
paths:
a #-> b -> 1
a #-> c -> 2
a #-> b -> 1
a #-> c -> 3
But judging from the previous discussions, there is a demand for a bit
different behavior, when `jsonb_minus` is operating only on the top level of
jsonb objects. For that purpose I suggest introducing a flag `deep`, that
should be false by default (as for `jsonb_concat`), that will allow to enable a
"deep logic" (a.k.a relative complement) I described above. With `deep=False`
this function will behave similar to `hstore`:
{"a": 1, "b": {"c": 2}} - {"a": 1, "b": {"c": 3}}
=> {"a": 1}
# jsonb_concat
We already have this function implemented, but a "deep" mode is missing.
```
jsonb_concat(jsonb, jsonb, deep=False)
```
Basically we're taking all the paths inside all jsonb objects and override
duplicated paths in the left one, then add all unique paths from right one to
the result.
Here are few examples for deep mode ("||" operator is just for the sake of
readability):
------------------------------------------------------------------------------
{"a": 1, "b": {"c": 2}} || {"a": 1, "b": {"d": 3}}
=> {"a": 1, "b": {"c": 2, "d": 3}}
paths:
a -> 1
b .-> c -> 2
a -> 1
b .-> d -> 3
------------------------------------------------------------------------------
{"a": 1, "b": {"c": 2}} || {"a": 1, "b": {"c": 3}}
=> {"a": 1, "b": {"c": 3}}
paths:
a -> 1
b .-> c -> 2
a -> 1
b .-> c -> 3
------------------------------------------------------------------------------
{"a": [1, 2, 3]} || {"a": [3, 4]}
=> {"a": [1, 2, 3, 4]}
paths:
a #-> 1
a #-> 2
a #-> 3
a #-> 3
a #-> 4
What do you think about that?
pgsql-hackers by date: