čt 11. 10. 2018 v 22:48 odesílatel Dmitry Dolgov <9erthali...@gmail.com>
napsal:
> > On Wed, 10 Oct 2018 at 14:26, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
> >
> > I am playing with this feature little bit
>
> Thanks a lot!
>
> > I have one idea - can be possible to use integer subscript for record
> fields? It can helps with iteration over record.
> >
> > example:
> >
> > select ('{"a":{"a":[10,20]}}'::jsonb)[0];--> NULL, but can be more
> practical if it returns same like select
> ('{"a":{"a":[10,"20"]}}'::jsonb)['a'];
>
> Sounds interesting, but I'm not sure how consistent it would be with the
> rest
> of jsonb functionality, and someone may want to get an error in this case.
> At
> the same time I believe that this can be achieved quite nicely with
> json_query
> or json_table from SQL/JSON patch (see examples here [1]). What do you
> think
> about this approach?
>
In this case, I don't see any problem - the array or multidimensional array
can be indexed by numbers or by special keys. But numbers are natural every
time.
For me, SQL/JSON, JSONPath support is different topic. More - the generic
support can be used for other types than Jsonb. I can imagine integrated
dictionary type - and the SQL/JSON support doesn't help here.
This is not too strong theme for me - just I don't see a reason for strong
restrictivity there.
> > I don't like quite ignoring bad subsript in update
>
> Can you show an example of such ignoring of a bad subsript in an update?
>
> > postgres=# insert into test(v) values( '[]');
> > INSERT 0 1
> > postgres=# update test set v[1000] = 'a';
> > UPDATE 1
> > postgres=# update test set v[1000] = 'a';
> > UPDATE 1
> > postgres=# update test set v[1000] = 'a';
> > UPDATE 1
> > postgres=# select * from test;
> > ┌────┬─────────────────┐
> > │ id │ v │
> > ╞════╪═════════════════╡
> > │ │ ["a", "a", "a"] │
> > └────┴─────────────────┘
> > (1 row)
> >
> > It should to raise exception in this case. Current behave allows append
> simply, but can be source of errors. For this case we can introduce some
> special symbol - some like -0 :)
>
> Yeah, it may look strange, but there is a reason behind it. I tried to
> keep the
> behaviour of this feature consistent with jsonb_set function (and in fact
> they're sharing the same functionality). And for jsonb_set it's documented:
>
> If the item (of a path, in our case an index) is out of the range
> -array_length .. array_length -1, and create_missing is true, the new
> value
> is added at the beginning of the array if the item is negative, and at
> the
> end of the array if it is positive.
>
> So, the index 1000 is way above the end of the array v, and every new item
> has
> being appended at the end.
>
> Of course no one said that they should behave similarly, but I believe it's
> quite nice to have consistency here. Any other opinions?
>
Aha - although I understand to your motivation, I am think so it is bad
design - and jsonb_set behave is not happy.
I am think so it is wrong idea, because you lost some information - field
position - I push value on index 10, but it will be stored on second
position.
Regards
Pavel
> > It is maybe strange, but I prefer less magic syntax like
> >
> > update test set v['a']['a'] = v['a']['a'] || '1000';
> >
> > more readable than
> >
> > update test set v['a']['a'][1000000] = 1000;
>
> Yep, with this patch it's possible to use both ways:
>
> =# table test;
> v
> -------------------------
> {"a": {"a": [1, 2, 3]}}
> (1 row)
>
> =# update test set v['a']['a'] = v['a']['a'] || '1000';
> UPDATE 1
>
> =# table test;
> v
> -------------------------------
> {"a": {"a": [1, 2, 3, 1000]}}
> (1 row)
>
> > My first impression is very good - update jsonb, xml documents can be
> very friendly.
>
> Thanks!
>
> 1:
> https://www.postgresql.org/message-id/flat/732208d3-56c3-25a4-8f08-3be1d54ad...@postgrespro.ru
>
