kingbase数据json操作：表转json、json转表、节点查询、节点添加

1、json_array_elements(json)
这个函数将JSON数组转换为行集合。例如：SELECT json_array_elements('[1, 2, 3]') AS element; 将返回一个包含每个数组元素的行。

2、json_each(json)
这个函数将JSON对象展开为(key, value)对。例如：SELECT * FROM json_each('{"a": 1, "b": 2}'); 将返回两行，一行包含键"a"和值1，另一行包含键"b"和值2。


3、json_array_length(json)
返回JSON数组的长度。例如：SELECT json_array_length('[1, 2, 3]'); 将返回3。

4、json_typeof(json)
返回JSON值的类型。例如：SELECT json_typeof('{"a": 1}'); 将返回"object"。
SELECT json_typeof('[1, 2, 3]');

5、json_agg(expression) 用来组件数组对象
聚合函数，用于将多行结果集聚合为一个JSON数组。例如：SELECT json_agg(name) FROM example_table; 将返回一个包含表中所有name字段值的JSON数组。

6、json_build_object(key, value, ...)
构建一个JSON对象。键值对作为参数传入。例如：SELECT json_build_object('name', 'John', 'age', 30); 将返回{"name": "John", "age": 30}。

7、json_build_array(value, ...)
构建一个JSON数组。值作为参数传入。例如：SELECT json_build_array('John', 30); 将返回["John", 30]。

8、json_value() 根据指定路径查找json值
DECLARE jsonData JSON ;
BEGIN
jsonData := '{"rootName":"11","chileList":[{"chileName":"c1","chileValue":"cv1"},{"chileName":"c2","chileValue":"cv2"}]}';
SELECT JSON_VALUE( jsonData :: jsonb, '$.chileList[0].chileName' );
end


9、JSON_QUERY 根据指定路径查找json值、json对象、json变量，并可以写判断条件
DECLARE jsonData JSON ;
BEGIN
jsonData := '{"rootName":"11","chileList":[{"chileName":"c1","chileValue":"cv1"},{"chileName":"c2","chileValue":"cv2"}]}';
SELECT JSON_query( jsonData :: jsonb, '$.chileList' );
end
SELECT JSON_query(jsonb '{"a": 1, "b": 2}', '$.a' );
SELECT JSON_query(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > 1)' );

10、jsonb_insert()  插入节点
--插入数组值
SELECT jsonb_insert('{"a": [0,1,2]}', '{a,1}', '"new_value"');  --向json的a节点的第一个下边插入值

--插入键值对
SELECT jsonb_insert('{"name": "John", "age": 30}'::jsonb, '{city}', '"New York"'::jsonb); 
SELECT jsonb_insert('{"person": {"name": "John"}}'::jsonb, '{person,age}', '"11"'::jsonb);
SELECT jsonb_insert(
    '{"person": {"name": "John", "age": 30, "address": {"city": "New York", "postal_code": "10001"}}}'::jsonb,
    '{person, address,street}',
    '"123 Main St"'::jsonb
);
--插入json对象
SELECT jsonb_insert(
    '{"person": {"name": "John", "age": 30}}'::jsonb,
    '{address}',
    '{"city": "New York", "postal_code": "10001"}'::jsonb
);
SELECT jsonb_insert(
    '{"person": {"name": "John", "age": 30}}'::jsonb,
    '{person,address}',
    '{"city": "New York", "postal_code": "10001"}'::jsonb
);

--插入json数组对象
SELECT jsonb_insert(
    '{"person": {"name": "John", "age": 30}}'::jsonb,
    '{address}',
    '["a","b","c"]'::jsonb
);


----------------------------------------------json转表示例
1、使用json_table转表简单示例
SELECT t.* from 
json_table(
'{"a":{"b":"c"}}'::jsonb,
'$.a' columns(
b text format json path '$.b')
)t;

2、操作json对象数组取值转表
DECLARE jsonData JSON;
BEGIN
jsonData := '{"rootName":"11","chileList":[{"chileName":"c1","chileValue":"cv1"},{"chileName":"c2","chileValue":"cv2"}],"chileObject2":{"chileName2":"c12","chileValue2":"cv12"}}';
SELECT jt.* FROM JSON_TABLE(
jsonData::jsonb,
'$[*]' COLUMNS (
id FOR ORDINALITY,
root_name varchar(100) PATH '$.rootName',
chileName2 varchar(100) PATH '$.chileObject2.chileName2',
chileValue2 varchar(100) PATH '$.chileObject2.chileValue2',
nested path ' $.chileList[*]' as pb columns (chileName varchar(100) path '$.chileName',chileValue varchar(100) path '$.chileValue')
)
)jt; 

END

3、多级多类json转化
DECLARE jsonData2 jsonb ;
BEGIN
jsonData2 := '{"body":{"container_code":"LSSC100082","out_order_code":"LSSC1000821600152532000","road_number_list":[{"road_number":132},{"road_number":125},{"road_number":124}],"send_date":1692166702363,"type":"1","major_cateory_code":"10"},"header":{"warehouse_code":"WMWHSE4"}}';
SELECT jt.* FROM JSON_TABLE(
jsonData2, '$' COLUMNS (
warehouse_code varchar(100) PATH '$.header.warehouse_code',
container_code varchar(100) PATH '$.body.container_code',
out_order_code varchar(100) PATH '$.body.out_order_code',
major_cateory_code varchar(100) PATH '$.body.major_cateory_code',
--nested path '$.header' as objectdata columns ( warehouse_code varchar(100) path '$.warehouse_code'),
nested path '$.body.road_number_list[*]' as listdata columns ( road_number varchar(100) path '$.road_number')
)

)jt ;

END
------------------------------------------------------------------表转json
1、将多行数据转化为json数组
SELECT JSON_OBJECT('name' : first_name || ' ' || last_name,'email' : email,'phone' : phone_number,'hire_date' : hire_date)
FROM hr.employees WHERE employee_id = 140;

2、表数据转化为多层json
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'IT');

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2);

--返回表中全部数据
SELECT json_build_object(
    'departments', (SELECT json_agg(departments) FROM departments),
    'employees', (SELECT json_agg(employees) FROM employees)
) FROM dual;

--对表数据进行筛选,添加键值对;如果要构造子json对象，需要嵌套分开来写
DECLARE root_kv varchar(100);
BEGIN
    root_kv := 'test';
SELECT json_build_object(
    'root',root_kv,
    'array', (SELECT array_agg(employee_id) FROM employees),
    'json_array', (SELECT json_agg(b) FROM (SELECT employee_name,department_id FROM employees )b)
) FROM dual;
end