Quelle  json_normalize.result   Sprache: Lisp

set names utf8;
create table t1 (json json);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
  `json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`json`))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert into t1 values
('{  }'),
('[  ]'),
('{ "foo" : "bar" }'),
('{ "foo" : "bar", "baz" : "whatever" }'),
('[ 1.2, 0.0, "text", 0, null, true, false ]'),
('[ "string", { "key" : "val", "a" : "b", "c" : [ 10, 9, 8, "seven", 11 ] }]'),
('{ "ăț€": "val1", "âț€":"val2" }');
select json, json_normalize(json) from t1
order by json;
json json_normalize(json)
[  ] []
[ "string", { "key" : "val", "a" : "b", "c" : [ 10, 9, 8, "seven", 11 ] }] ["string",{"a":"b","c":[1.0E1,9.0E0,8.0E0,"seven",1.1E1],"key":"val"}]
[ 1.2, 0.0, "text", 0, null, true, false ] [1.2E0,0.0E0,"text",0.0E0,null,true,false]
{  } {}
{ "foo" : "bar" } {"foo":"bar"}
{ "foo" : "bar", "baz" : "whatever" } {"baz":"whatever","foo":"bar"}
{ "ăț€": "val1", "âț€":"val2" } {"âț€":"val2","ăț€":"val1"}
create view v1 as (select json, json_normalize(json) norm_json from t1);
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`json` AS `json`,json_normalize(`t1`.`json`) AS `norm_json` from `t1`) utf8mb4 utf8mb4_uca1400_ai_ci
select * from v1
order by json;
json norm_json
[  ] []
[ "string", { "key" : "val", "a" : "b", "c" : [ 10, 9, 8, "seven", 11 ] }] ["string",{"a":"b","c":[1.0E1,9.0E0,8.0E0,"seven",1.1E1],"key":"val"}]
[ 1.2, 0.0, "text", 0, null, true, false ] [1.2E0,0.0E0,"text",0.0E0,null,true,false]
{  } {}
{ "foo" : "bar" } {"foo":"bar"}
{ "foo" : "bar", "baz" : "whatever" } {"baz":"whatever","foo":"bar"}
{ "ăț€": "val1", "âț€":"val2" } {"âț€":"val2","ăț€":"val1"}
select json_normalize(NULL);
json_normalize(NULL)
NULL
select json_normalize('{ "invalid": "no_close"');
json_normalize('{ "invalid": "no_close"')
NULL
Warnings:
Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_normalize'
drop table t1;
drop view v1;
create table t1 (text varchar(200) character set 'latin1');
insert into t1 values (unhex('22E522'));
create table t2 (text varchar(200) character set 'utf8mb4');
insert into t2 SELECT * FROM t1;
select t1.text, hex(t1.text) from t1;
text hex(t1.text)
"å" 22E522
select t2.text, hex(t2.text) from t2;
text hex(t2.text)
"å" 22C3A522
select t1.text
, t2.text
, replace(json_normalize(t1.text), unhex('C3A5'), unhex('C385'))
, replace(json_normalize(t2.text), unhex('C3A5'), unhex('C385'))
, hex(replace(json_normalize(t1.text), unhex('C3A5'), unhex('C385')))
, hex(replace(json_normalize(t2.text), unhex('C3A5'), unhex('C385')))
from t1, t2;
text text replace(json_normalize(t1.text), unhex('C3A5'), unhex('C385')) replace(json_normalize(t2.text), unhex('C3A5'), unhex('C385')) hex(replace(json_normalize(t1.text), unhex('C3A5'), unhex('C385'))) hex(replace(json_normalize(t2.text), unhex('C3A5'), unhex('C385')))
"å" "å" "Å" "Å" 22C38522 22C38522
drop table t1;
drop table t2;
create table t1 (text varchar(1));
insert into t1 values ('0');
select concat_ws(' ', t1.text, t1.text) from t1;
concat_ws(' ', t1.text, t1.text)
0 0
select concat_ws(' ', json_normalize(t1.text), json_normalize(t1.text)) from t1;
concat_ws(' ', json_normalize(t1.text), json_normalize(t1.text))
0.0E0 0.0E0
drop table t1;
#
# MDEV-28922 JSON_NORMALIZE handling of duplicate keys
#
SET @j = '{"x": 1, "y": 2, "z": 3, "b": 0, "c": 5, "b":3, "a": 4, "b": 1, "a": 3, "a": 2, "a": 1, "b": 2}';
SELECT @k := JSON_NORMALIZE(@j);
@k := JSON_NORMALIZE(@j)
{"a":1.0E0,"a":2.0E0,"a":3.0E0,"a":4.0E0,"b":0.0E0,"b":1.0E0,"b":2.0E0,"b":3.0E0,"c":5.0E0,"x":1.0E0,"y":2.0E0,"z":3.0E0}
SELECT JSON_EXTRACT(@j, '$.b') as j1, JSON_EXTRACT(@k, '$.b') as j2;
j1 j2
0 0.0E0
Primitives
SET @j='{"a": null, "a": true, "a": false, "a": 123, "a": "string"}';
SELECT JSON_NORMALIZE(@j);
JSON_NORMALIZE(@j)
{"a":"string","a":1.23E2,"a":true,"a":false,"a":null}
SET @j2='{"a": "string", "a": 123, "a": false, "a": true, "a": null}';
SELECT JSON_NORMALIZE(@j2);
JSON_NORMALIZE(@j2)
{"a":"string","a":1.23E2,"a":true,"a":false,"a":null}
SELECT JSON_EQUALS(@j, @j2) je;
je
1
Objects
SET @j='{
  "a": {},
  "a": {"x":1},
  "a": {"x":1,"y":2},
  "a": {"x":1,"y":2,"z":3}
}';
SELECT JSON_NORMALIZE(@j);
JSON_NORMALIZE(@j)
{"a":{},"a":{"x":1.0E0},"a":{"x":1.0E0,"y":2.0E0},"a":{"x":1.0E0,"y":2.0E0,"z":3.0E0}}
SET @j2='{
  "a": {"x":1,"y":2,"z":3},
  "a": {"x":1},
  "a": {},
  "a": {"x":1,"y":2}
}';
SELECT JSON_NORMALIZE(@j2);
JSON_NORMALIZE(@j2)
{"a":{},"a":{"x":1.0E0},"a":{"x":1.0E0,"y":2.0E0},"a":{"x":1.0E0,"y":2.0E0,"z":3.0E0}}
SELECT JSON_EQUALS(@j, @j2) as je;
je
1
Arrays
SET @j='{
  "a": [],
  "a": [1],
  "a": [1,2],
  "a": [1,2,3],
  "a": [1,2,3,4]
}';
SELECT JSON_NORMALIZE(@j);
JSON_NORMALIZE(@j)
{"a":[],"a":[1.0E0],"a":[1.0E0,2.0E0],"a":[1.0E0,2.0E0,3.0E0],"a":[1.0E0,2.0E0,3.0E0,4.0E0]}
SET @j2='{
  "a": [1,2,3],
  "a": [],
  "a": [1,2,3,4],
  "a": [1],
  "a": [1,2]
}';
SELECT JSON_NORMALIZE(@j2);
JSON_NORMALIZE(@j2)
{"a":[],"a":[1.0E0],"a":[1.0E0,2.0E0],"a":[1.0E0,2.0E0,3.0E0],"a":[1.0E0,2.0E0,3.0E0,4.0E0]}
SELECT JSON_EQUALS(@j, @j2) je;
je
1
Mixed Arrays
SET @j='{
  "a": [],
  "a": [null],
  "a": [1,"x",true],
  "a": [{"k":1}],
  "a": [[1,2],[3,4]]
}';
SELECT JSON_NORMALIZE(@j);
JSON_NORMALIZE(@j)
{"a":[],"a":[{"k":1.0E0}],"a":[null],"a":[[1.0E0,2.0E0],[3.0E0,4.0E0]],"a":[1.0E0,"x",true]}
SET @j2='{
  "a": [[1,2],[3,4]],
  "a": [{"k":1}],
  "a": [],
  "a": [1,"x",true],
  "a": [null]
}';
SELECT JSON_NORMALIZE(@j2);
JSON_NORMALIZE(@j2)
{"a":[],"a":[{"k":1.0E0}],"a":[null],"a":[[1.0E0,2.0E0],[3.0E0,4.0E0]],"a":[1.0E0,"x",true]}
SELECT JSON_EQUALS(@j, @j2) je;
je
1
Nested objects
SET @j='{
  "a": {"x":{"y":1}},
  "a": {"x":{"y":[1,2]}},
  "a": {"x":{"y":{"z":3}}},
  "a": {"x":{}}
}';
SELECT JSON_NORMALIZE(@j);
JSON_NORMALIZE(@j)
{"a":{"x":{}},"a":{"x":{"y":{"z":3.0E0}}},"a":{"x":{"y":[1.0E0,2.0E0]}},"a":{"x":{"y":1.0E0}}}
SET @j2='{
  "a": {"x":{}},
  "a": {"x":{"y":{"z":3}}},
  "a": {"x":{"y":1}},
  "a": {"x":{"y":[1,2]}}
}';
SELECT JSON_NORMALIZE(@j2);
JSON_NORMALIZE(@j2)
{"a":{"x":{}},"a":{"x":{"y":{"z":3.0E0}}},"a":{"x":{"y":[1.0E0,2.0E0]}},"a":{"x":{"y":1.0E0}}}
SELECT JSON_EQUALS(@j, @j2) je;
je
1
Everything mixed
SET @j='{
  "a": null,
  "a": 1,
  "a": "abc",
  "a": true,
  "a": [],
  "a": [1],
  "a": [1,2],
  "a": {},
  "a": {"k":1},
  "a": {"k":1,"m":2}
}';
SELECT JSON_NORMALIZE(@j);
JSON_NORMALIZE(@j)
{"a":{},"a":{"k":1.0E0},"a":{"k":1.0E0,"m":2.0E0},"a":[],"a":[1.0E0],"a":[1.0E0,2.0E0],"a":"abc","a":1.0E0,"a":true,"a":null}
SET @j2='{
  "a": {"k":1},
  "a": [],
  "a": true,
  "a": {"k":1,"m":2},
  "a": "abc",
  "a": null,
  "a": [1,2],
  "a": 1,
  "a": {},
  "a": [1]
}';
SELECT JSON_NORMALIZE(@j2);
JSON_NORMALIZE(@j2)
{"a":{},"a":{"k":1.0E0},"a":{"k":1.0E0,"m":2.0E0},"a":[],"a":[1.0E0],"a":[1.0E0,2.0E0],"a":"abc","a":1.0E0,"a":true,"a":null}
SELECT JSON_EQUALS(@j, @j2) je;
je
1
# End of 10.11 tests