Обсуждение: a bug jsonb?

Поиск
Список
Период
Сортировка

a bug jsonb?

От
Станислав Губанов
Дата:
Hello!

I'd like to clarify, if this a bug or a feature

simple JSON 
{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}

Table for data
create table test_json (json_data jsonb); 

put json into table 
INSERT INTO test_json (json_data) VALUES ('{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}');

order of key in json is changed in such query:
SELECT json_data FROM test_json;
expected result: {"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}
actual result: {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}

----- console log ----- 
test=# create table test_json (json_data jsonb);
CREATE TABLE
test=# INSERT INTO test_json (json_data) VALUES ('{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}');
INSERT 0 1
test=# select json_data from test_json;
                      json_data
------------------------------------------------------
 {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}
(1 row)

test=# drop table test_json;
DROP TABLE

--
Sincerely, Stanislav!

Re: a bug jsonb?

От
Josef Šimánek
Дата:
If I understand well your question, this is expected with JSONB.

see https://www.postgresql.org/docs/current/datatype-json.html for more info:

...jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept...

čt 22. 11. 2018 v 15:59 odesílatel Станислав Губанов <gubanovss@gmail.com> napsal:
Hello!

I'd like to clarify, if this a bug or a feature

simple JSON 
{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}

Table for data
create table test_json (json_data jsonb); 

put json into table 
INSERT INTO test_json (json_data) VALUES ('{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}');

order of key in json is changed in such query:
SELECT json_data FROM test_json;
expected result: {"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}
actual result: {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}

----- console log ----- 
test=# create table test_json (json_data jsonb);
CREATE TABLE
test=# INSERT INTO test_json (json_data) VALUES ('{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}');
INSERT 0 1
test=# select json_data from test_json;
                      json_data
------------------------------------------------------
 {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}
(1 row)

test=# drop table test_json;
DROP TABLE

--
Sincerely, Stanislav!

Re: a bug jsonb?

От
Francisco Olarte
Дата:
On Thu, Nov 22, 2018 at 3:59 PM Станислав Губанов <gubanovss@gmail.com> wrote:
....
> order of key in json is changed in such query:
> SELECT json_data FROM test_json;
> expected result: {"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}
> actual result: {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}

From https://www.postgresql.org/docs/11/datatype-json.html

"There are two JSON data types: json and jsonb. They accept almost
identical sets of values as input. The major practical difference is
one of efficiency. The json data type stores an exact copy of the
input text, which processing functions must reparse on each execution;
while jsonb data is stored in a decomposed binary format that makes it
slightly slower to input due to added conversion overhead, but
significantly faster to process, since no reparsing is needed. jsonb
also supports indexing, which can be a significant advantage.

Because the json type stores an exact copy of the input text, it will
preserve semantically-insignificant white space between tokens, as
well as the order of keys within JSON objects. Also, if a JSON object
within the value contains the same key more than once, all the
key/value pairs are kept. (The processing functions consider the last
value as the operative one.) By contrast, jsonb does not preserve
white space, does not preserve the order of object keys, and does not
keep duplicate object keys. If duplicate keys are specified in the
input, only the last value is kept."

Francisco Olarte.

Re: a bug jsonb?

От
Josef Šimánek
Дата:
the order is changed as well on your screenshot
čt 22. 11. 2018 v 16:23 odesílatel Станислав Губанов <gubanovss@gmail.com> napsal:
I think yes.
Please look at this screen shot
order of key in json not changed in pgAdmin, maybe exists some option for turn on/off this feature.

image.png

чт, 22 нояб. 2018 г. в 18:04, Josef Šimánek <josef.simanek@gmail.com>:
If I understand well your question, this is expected with JSONB.

see https://www.postgresql.org/docs/current/datatype-json.html for more info:

...jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept...

čt 22. 11. 2018 v 15:59 odesílatel Станислав Губанов <gubanovss@gmail.com> napsal:
Hello!

I'd like to clarify, if this a bug or a feature

simple JSON 
{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}

Table for data
create table test_json (json_data jsonb); 

put json into table 
INSERT INTO test_json (json_data) VALUES ('{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}');

order of key in json is changed in such query:
SELECT json_data FROM test_json;
expected result: {"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}
actual result: {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}

----- console log ----- 
test=# create table test_json (json_data jsonb);
CREATE TABLE
test=# INSERT INTO test_json (json_data) VALUES ('{"rootC":{},"rootB":{},"rootZ":{},"rootA":[]}');
INSERT 0 1
test=# select json_data from test_json;
                      json_data
------------------------------------------------------
 {"rootA": [], "rootB": {}, "rootC": {}, "rootZ": {}}
(1 row)

test=# drop table test_json;
DROP TABLE

--
Sincerely, Stanislav!


--
С уважением, Станислав!
Вложения