func_json_extract.test

#Test cases of json_extract from the command line
select json_extract('{"a":1,"b":2,"c":3}','$.a');
select json_extract('{"a":1,"b":2,"c":3}','$.b');
select json_extract('{"a":{"q":[1,2,3]}}','$.a.q[1]');
select json_extract('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]','$[1].a');
select json_extract('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q[1]');
select json_extract('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q');
select json_extract('[1,2,3]','$[*]');
select json_extract('{"a":[1,2,3,{"b":4}]}','$.a[3].b');
select json_extract('{"a":[1,2,3,{"b":4}]}','$.a[3].c');
select json_extract('{"a":[1,2,3,{"b":4}],"c":5}','$.*');
select json_extract('{"a":[1,2,3,{"a":4}]}','$**.a');
select json_extract('{"a":[1,2,3,{"a":4}]}','$.a[*].a');
select json_extract('{"a":1}','$[0]');
select json_extract('{"a":1}','$[0].a');
select json_extract('{"a":1}','$[0].b');
select json_extract('{"a":1}','$[1]');
select json_extract('{"af": [1, "2", {"aaf": "bb"}],"eab":"888"}','$**.f');
select json_extract('{"a": [1, "2", {"a": "bb"}]}','$**.a');
select  json_extract('{"a":"a1","b":"b1"}','$.**');
select  json_extract('{"a":"a1","b":"b1"}','$**.1');

# Test cases of json_extract from column
drop table if exists t1;
create table t1 (a json,b int);
insert into t1(a,b) values ('{"a":1,"b":2,"c":3}',1);
select json_extract(t1.a,'$.a') from t1 where t1.b=1;
insert into t1(a,b) values ('{"a":4,"b":5,"c":6}',2);
select json_extract(t1.a,'$.b') from t1 where t1.b=2;
select json_extract(t1.a,'$.a') from t1;
insert into t1(a,b) values ('{"a":{"q":[1,2,3]}}',3);
select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=3;
insert into t1(a,b) values ('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]',4);
select json_extract(t1.a,'$[1].a') from t1 where t1.b=4;
insert into t1(a,b) values ('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}',5);
select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=5;
select json_extract(t1.a,'$.a.q') from t1 where t1.b=5;
insert into t1(a,b) values ('[1,2,3]',6);
select json_extract(t1.a,'$[*]') from t1 where t1.b=6;
insert into t1(a,b) values ('{"a":[1,2,3,{"b":4}]}',7);
select json_extract(t1.a,'$.a[3].b') from t1 where t1.b=7;
select json_extract(t1.a,'$.a[3].c') from t1 where t1.b=7;
insert into t1(a,b) values ('{"a":[1,2,3,{"b":4}],"c":5}',8);
select json_extract(t1.a,'$.*') from t1 where t1.b=8;
insert into t1(a,b) values ('{"a":[1,2,3,{"a":4}]}',9);
select json_extract(t1.a,'$**.a') from t1 where t1.b=9;
select json_extract(t1.a,'$.a[*].a') from t1 where t1.b=9;
drop table t1;
create table t1 (a json);
insert into t1(a) values ('{"a":1}'),('[1,2]'),('{"xa":1}');
drop table if exists t2;
create table t2 (a varchar(100));
insert into t2 values ('$[0]'),('$.a');
select json_extract(t1.a,t2.a) qqq,t1.a,t2.a from t2, t1;
drop table if exists json_table_1;
create table json_table_1 (j1 json);
insert into json_table_1 values('{"key10": "value1", "key2": "value2"}'),('{"key1": "@#$_%^&*()!@", "123456": "中文mo"}'),('{"芝士面包": "12abc", "123456": "中文mo"}'),('{"": "", "123456": "中文mo"}'),('{"a 1": "b 1", "123456": "中文mo"}'),('{"d1": "2020-10-09", "d2": "2019-08-20 12:30:00"}'),('{"d1": [true,false]}'),('{}');
select json_extract('{"a":"a1","b":"b1"}','$.*') from json_table_1;
create view v1 as select json_extract('{"a":1}','$.a');
desc v1;

select json_extract('{"a":1}',null);
select json_extract(null,'$');
select json_extract(null,null);
select json_extract('{"a":1}',null) from json_table_1;
select json_extract(null,'$') from json_table_1;
select json_extract('[1,2,3]','$[last]');
select json_extract('[1,2,3]','$[last-1]');
select json_extract('[1,2,3]','$[last-2]');
select json_extract('[1,2,3]','$[last-3]');
select json_extract('[1,2,3]','$[0 to 2]');
select json_extract('[1,2,3]','$[0 to last]');
select json_extract('[1,2,3]','$[0 to last-1]');
select json_extract('[1,2,3]','$[last-2 to last]');
select json_extract('[1,2,3]','$[last-1 to last-2]');
select json_extract('[1,2,3]','$[last-8 to last-2]');
select json_extract('[1,2,3]','$[last-2 to last-8]');
select json_extract('[1,2,3]','$[0 to last-8]');

# Test multiple paths
select json_extract('{"a":1,"b":2,"c":3}','$.a','$.b');
select json_extract('{"a":1,"b":2,"c":3}','$.a','$.b','$.c');
select json_extract('{"a":1,"b":2,"c":3}','$.c','$.d');
select json_extract('[0,1,2]', '$[0]', '$[1]');
select json_extract('[0,1,2]', '$[1]', '$[0]');
select json_extract('[0,1,2]', '$[last-1]', '$[0]', '$[2]');

select json_extract('[0,1,2]','$[4]');
select json_extract('[0,1,2]','$[100]');
select json_extract('[0,234,32432,423,5234,11443242,44242342424,23424323]','$[2000]');

# json_extract_string/float64
select json_extract_string('{"a":1,"b":2,"c":3}','$.a');
select json_extract_string('{"a":1,"b":2,"c":3}','$.b');
select json_extract_string('{"a":"x","b":"y","c":"z"}','$.a');
select json_extract_string('{"a":"x","b":"y","c":"z"}','$.b');
select json_extract_string('{"a":{"q":[1,2,3]}}','$.a.q[1]');
select json_extract_string('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]','$[1].a');
select json_extract_string('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q[1]');
select json_extract_string('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q');
select json_extract_string('[1,2,3]','$[*]');
select json_extract_string('{"a":[1,2,3,{"b":4}]}','$.a[3].b');
select json_extract_string('{"a":[1,2,3,{"b":4}]}','$.a[3].c');
select json_extract_string('{"a":[1,2,3,{"b":4}],"c":5}','$.*');
select json_extract_string('{"a":[1,2,3,{"a":4}]}','$**.a');
select json_extract_string('{"a":[1,2,3,{"a":4}]}','$.a[*].a');
select json_extract_string('{"a":1}','$[0]');
select json_extract_string('{"a":1}','$[0].a');
select json_extract_string('{"a":1}','$[0].b');
select json_extract_string('{"a":1}','$[1]');
select json_extract_string('{"af": [1, "2", {"aaf": "bb"}],"eab":"888"}','$**.f');
select json_extract_string('{"a": [1, "2", {"a": "bb"}]}','$**.a');
select json_extract_string('{"a":"a1","b":"b1"}','$.**');
select json_extract_string('{"a":"a1","b":"b1"}','$**.1');


select json_extract_float64('{"a":1,"b":2,"c":3}','$.a');
select json_extract_float64('{"a":1,"b":2,"c":3}','$.b');
select json_extract_float64('{"a":"x","b":"y","c":"z"}','$.a');
select json_extract_float64('{"a":"x","b":"y","c":"z"}','$.b');
select json_extract_float64('{"a":{"q":[1,2,3]}}','$.a.q[1]');
select json_extract_float64('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]','$[1].a');
select json_extract_float64('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q[1]');
select json_extract_float64('{"a":{"q":[{"a":1},{"a":2},{"a":3}]}}','$.a.q');
select json_extract_float64('[1,2,3]','$[*]');
select json_extract_float64('{"a":[1,2,3,{"b":4}]}','$.a[3].b');
select json_extract_float64('{"a":[1,2,3,{"b":4}]}','$.a[3].c');
select json_extract_float64('{"a":[1,2,3,{"b":4}],"c":5}','$.*');
select json_extract_float64('{"a":[1,2,3,{"a":4}]}','$**.a');
select json_extract_float64('{"a":[1,2,3,{"a":4}]}','$.a[*].a');
select json_extract_float64('{"a":1}','$[0]');
select json_extract_float64('{"a":1}','$[0].a');
select json_extract_float64('{"a":1}','$[0].b');
select json_extract_float64('{"a":1}','$[1]');
select json_extract_float64('{"af": [1, "2", {"aaf": "bb"}],"eab":"888"}','$**.f');
select json_extract_float64('{"a": [1, "2", {"a": "bb"}]}','$**.a');
select json_extract_float64('{"a":"a1","b":"b1"}','$.**');
select json_extract_float64('{"a":"a1","b":"b1"}','$**.1');

select json_extract_float64('{"a":123456789012345678901234567890,"b":2,"c":3}','$.a');
select json_extract_float64('{"a":-123456789012345678901234567890,"b":2,"c":3}','$.a');
select json_extract_float64('{"a":null,"b":2,"c":3}','$.a');
select json_extract_float64('{"a":NaN,"b":2,"c":3}','$.a');
select json_extract_float64('{"a":1e10,"b":2,"c":3}','$.a');
select json_extract_float64('{"a":3.1415926535897e1,"b":2,"c":3}','$.a');

drop table if exists jtags;
create table jtags(id int, tags json, metrics json);
insert into jtags values 
(1, '{"tag1": "xxx", "tag2": "yyy1", "tag13": "zzz"}', '{"metric1": 1, "metric2": 1.0, "metric13": 1}'),
(2, '{"tag1": "xxx", "tag2": "yyy2", "tag23": "zzz"}', '{"metric1": 2, "metric2": 2.0, "metric23": 2}'),
(3, '{"tag1": "xxx", "tag2": "yyy3", "tag33": "zzz"}', '{"metric1": 3, "metric2": 3.0, "metric33": 3}'),
(4, '{"tag1": "xxx", "tag2": "yyy4", "tag43": "zzz"}', '{"metric1": 4, "metric2": 4.0, "metric43": 4}'),
(5, '{"tag1": "xxx", "tag2": "yyy5", "tag53": "zzz"}', '{"metric1": 5, "metric2": 5.0, "metric53": 5}'),
(6, '{"tag1": "xxx", "tag2": "yyy6", "tag63": "zzz"}', '{"metric1": 6, "metric2": 6.0, "metric63": 6}'),
(7, '{"tag1": "xxx", "tag2": "yyy7", "tag73": "zzz"}', '{"metric1": 7, "metric2": 7.0, "metric73": 7}'),
(8, '{"tag1": "xxx", "tag2": "yyy8", "tag83": "zzz"}', '{"metric1": 8, "metric2": 8.0, "metric83": 8}'),
(9, '{"tag1": "xxx", "tag2": "yyy9", "tag93": "zzz"}', '{"metric1": 9, "metric2": 9.0, "metric93": 9}');

select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags;
select count(json_extract_float64(jtags.metrics, '$.metric1')) c1, count(json_extract_float64(jtags.metrics, '$.metric33')) c33 from jtags;
select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags
where json_extract_string(jtags.tags, '$.tag1') = 'xxx';
select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags
where json_extract_string(jtags.tags, '$.tag2') = 'yyy3';
select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags
where json_extract_string(jtags.tags, '$.tag2') = 'yyy5';
select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags
where json_extract_string(jtags.tags, '$.tag33') = 'zzz';
select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags
where json_extract_string(jtags.tags, '$.tag53') = 'zzz';
select sum(json_extract_float64(jtags.metrics, '$.metric1')) s1, sum(json_extract_float64(jtags.metrics, '$.metric33')) s33 from jtags
where json_extract_string(jtags.tags, '$.tag35') = 'zzz';


create database if not exists test;
use test;
create table json_tab (a json);
insert into json_tab values ('{"CODE": "BOARDCODE-3", "LINE": "BOARDLINE-0", "PANEL": "BOARDPANEL-69"}');
select * from json_tab;
select json_extract_string(a, '$.LINE') from json_tab;
select a from json_tab where json_extract_string(a, '$.LINE') = 'BOARDLINE-0';
select a from json_tab where json_extract_string(a, '$.LINE') = '"BOARDLINE-0"';
drop database test;

create database if not exists test;
use test;
CREATE TABLE test_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_data JSON NOT NULL
);

INSERT INTO test_json (json_data)
VALUES
    ('{"name": "Alice", "age": 25}'),
    ('{"name": "Bob", "age": 30}'),
    ('{"name": "Charlie", "age": 22}');

SELECT
    id,
    json_data,
    json_extract_string(json_data, '$.name') AS extracted_name
FROM test_json;

drop database test;

create database if not exists test;
use test;
drop table if exists t1;
create table t1(c1 json);

insert into t1 values ('{"area":"A"}');
insert into t1 values ('{"area":"B"}');
insert into t1 values ('{"area":"C"}');
insert into t1 values ('{"area":"D","length":10.25}');
insert into t1 values ('{"area":"E","length":20}');

select * from t1;
select c1, json_extract(c1, '$.area'), json_extract_string(c1, '$.area') from t1;
select c1, json_extract(c1, '$.area'), json_extract_string(c1, '$.area'), json_extract_float64(c1, '$.length') from t1;
select json_extract_float64(c1, '$.length') from t1;
select json_extract_float64(c1, '$.length') from t1 where json_extract_string(c1, '$.area') = 'E';

CREATE TABLE test_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_data JSON NOT NULL
);

INSERT INTO test_json (json_data)
VALUES
    ('{"number": 25}'),
    ('{"number": 25.5}'),
    ('{"number": "25"}'),
    ('{"number": "25.5"}'),
    ('{"number": "hello"}'),
    ('{"number": true}'),
    ('{"number": null}'),
    ('{"number": [1, 2, 3]}'),
    ('{"number": {"value": 25}}');

SELECT
    id,
    json_data,
    json_extract_float64(json_data, '$.number') AS extracted_number
FROM test_json;

drop table if exists test_json;

CREATE TABLE test_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_data JSON NOT NULL
);

INSERT INTO test_json (json_data)
VALUES
    ('{"name": "Alice", "age": 25}'),
    ('{"name": "Bob", "age": 25.5}'),
    ('{"name": "Charlie", "age": "25"}'),
    ('{"name": "David", "age": "25.5"}'),
    ('{"name": "Eve", "age": "hello"}'),
    ('{"name": "Frank", "age": true}'),
    ('{"name": "Grace", "age": null}'),
    ('{"name": "Hank", "age": [1, 2, 3]}'),
    ('{"name": "Ivan", "age": {"value": 25}}');


SELECT
    id,
    json_data,
    json_extract_string(json_data, '$.name') AS extracted_name,
    json_extract_float64(json_data, '$.age') AS extracted_age
FROM test_json;

CREATE TABLE test_json_simplified (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_data JSON NOT NULL
);

INSERT INTO test_json_simplified (json_data)
VALUES
    ('{"name": "Alice", "age": 25, "scores": {"math": 85.0, "science": 90.0}}'),
    ('{"name": "Bob", "age": 25, "scores": {"math": 80.5, "science": 88.0}}'),
    ('{"name": "Charlie", "age": 25, "scores": {"math": null, "science": null}}'),
    ('{"name": "David", "age": 25, "scores": {"math": 80.5, "science": null}}'),
    ('{"name": "Eve", "age": 25, "scores": {"math": null, "science": 92.0}}'),
    ('{"name": "Frank", "age": 25, "scores": {"math": 85.0, "science": 88.0}}'),
    ('{"name": "Grace", "age": 25, "scores": {"math": null, "science": null}}'),
    ('{"name": "Hank", "age": 25, "scores": {"math": 80.5, "science": 88.0}}'),
    ('{"name": "Ivan", "age": 25, "scores": {"math": null, "science": null}}');

SELECT
    id,
    json_extract_string(json_data, '$.name') AS extracted_name,
    json_extract_float64(json_data, '$.age') AS extracted_age,
    json_extract_float64(json_data, '$.scores.math') AS extracted_math_score,
    json_extract_float64(json_data, '$.scores.science') AS extracted_science_score
FROM test_json_simplified;

drop database test;

create database test;
use test;
create table test_123(c1 json);
insert into test_123 values ('{"a1":10, "a2":20}');
insert into test_123 values ('{"a1":"test", "a2":"test2"}');
select * from test_123;
select json_extract(c1, '$.a1', '$.a2') from test_123;
select json_extract_float64(c1, '$.a1', '$.a2') from test_123;
select json_extract_string(c1, '$.a1', '$.a2') from test_123;
drop database test;


create database test;
use test;

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON NOT NULL
);
INSERT INTO example_table (data)
VALUES (
    '{"CODE": "CODE-3", "LINE": "LINE-4", "PANEL": "PANEL-17", "item1": "value1", "item2": "value2", "item3": "value3", "item4": "value4", "item5": "value5", "item6": "value6", "item7": "value7", "item8": "value8", "item9": "value9", "item10": "value10", "item11": "value11", "item12": "value12", "item13": "value13", "item14": "value14", "item15": "value15", "item16": "value16", "item17": "value17", "item18": "value18", "item19": "value19", "item20": "value20"}'
);
select * from example_table where json_extract(data, '$.CODE') = 'CODE-3';
select * from example_table where json_extract(data, '$.LINE') = 'LINE-4';
select * from example_table where json_extract(data, '$.PANEL') = 'PANEL-17';
drop database test;