JSON 处理 · PHP/Python/前端/Linux 等等学习笔记

[TOC] ## 概述 SQLite 的 JSON 扩展允许你在 SQLite 数据库中存储、查询和操作 JSON 数据。SQLite 从版本 3.9.0 开始引入了对 JSON 数据的支持，使其能够直接在 SQL 查询中处理 JSON 数据。 ## 函数 ### json 1. json(X) 函数验证其参数 X 是否为有效的 JSON 字符串或 JSONB blob， 2. 如果是json 则返回 JSON 字符串的缩小版本,其中删除了所有不必要的空格。 3. 如果 X 不是格式正确的 JSON 字符串或 JSONB blob，则此例程会抛出错误 ``` select json(' { "this" : "is", "a": [ "test" ] } ') // output {"this":"is","a":["test"]} ``` ### json_pretty 使 JSON 结果更易于人类阅读 ### json_array 组合成 json 数组 1. 接收一个或多个参数,并返回这些参数组成的 JSON 数组 ``` json_array(1,2,'3',4) → '[1,2,"3",4]' json_array('[1,2]') → '["[1,2]"]' json_array(json_array(1,2)) → '[[1,2]]' json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) → '[1,null,"3",[4,5],{"six":7.7}]' ``` ### json_object 组合成json 对象示例 ``` json_object('a',2,'c',4) → '{"a":2,"c":4}' json_object('a',2,'c','{e:5}') → '{"a":2,"c":"{e:5}"}' json_object('a',2,'c',json_object('e',5)) → '{"a":2,"c":{"e":5}}' ``` ### json_extract 1. 提取需要的字段值格式 ``` json_extract(X,P1,P2,...) ``` 示例 ``` json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') → '{"a":2,"c":[4,5,{"f":7}]}' json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') → '[4,5,{"f":7}]' json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') → '{"f":7}' json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') → 7 json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') → '[[4,5],2]' json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') → 5 json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') → '[null,2]' json_extract('{"a":"xyz"}', '$.a') → 'xyz' json_extract('{"a":null}', '$.a') → NULL ``` ### json_insert()、json_replace 和 json_set() 函数 | 功能 | 如果已经存在则覆盖？ | 如果不存在则创建？ | | --- | --- | --- | | json\_insert() | 不 | 是的 | | json\_replace() | 是的 | 不 | | json\_set() | 是的 | 是的 | 3. 第一个参数始终是要编辑的原始 JSON。后续参数成对出现，每对的第一个元素是路径，第二个元素是要在该路径上插入、替换或设置的值示例 1. 要将元素附加到数组末尾，请使用数组索引为“#”的 json_insert() ``` json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]' json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]' ``` 2. 其他示例： ``` json_insert('{"a":2,"c":4}', '$.a', 99) → '{"a":2,"c":4}' json_insert('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}' json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}' json_replace('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4}' json_set('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}' json_set('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}' json_set('{"a":2,"c":4}', '$.c', '[97,96]') → '{"a":2,"c":[97,96]"}' json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) → '{"a":2,"c":[97,96]}' json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) → '{"a":2,"c":[97,96]}' ``` ### json_remove 1. json_remove(X,P,...) 函数将单个 JSON 值作为其第一个参数，后跟零个或多个路径参数 2. son_remove(X,P,...) 函数返回 X 参数的副本，其中删除了路径参数标识的所有元素。选择在 X 中找不到的元素的路径将被忽略示例 ``` json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]' json_remove('[0,1,2,3,4]','$[2]','$[0]') → '[1,3,4]' json_remove('[0,1,2,3,4]','$[0]','$[2]') → '[1,2,4]' json_remove('[0,1,2,3,4]','$[#-1]','$[0]') → '[1,2,3]' json_remove('{"x":25,"y":42}') → '{"x":25,"y":42}' json_remove('{"x":25,"y":42}','$.z') → '{"x":25,"y":42}' json_remove('{"x":25,"y":42}','$.y') → '{"x":25}' json_remove('{"x":25,"y":42}','$') → NULL ``` ### json_patch 可以添加、修改或删除 JSON 对象的元素 1. json_patch() 例程是json_set()和json_remove()的通用替代品 2. MergePatch 不能附加到数组，也不能修改数组的单个元素。它只能将整个数组作为一个单元插入、替换或删除示例 ``` json_patch('{"a":1,"b":2}','{"c":3,"d":4}') → '{"a":1,"b":2,"c":3,"d":4}' json_patch('{"a":[1,2],"b":2}','{"a":9}') → '{"a":9,"b":2}' json_patch('{"a":[1,2],"b":2}','{"a":null}') → '{"b":2}' json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') → '{"a":9,"c":8}' json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') → '{"a":{"x":1,"y":9},"b":3,"c":8}' ``` ### json_array_length 1. json_array_length(X) 函数返回 JSON 数组 X 中的元素数量，如果 X 是某种 JSON 值而非数组，则返回 0 2. json_array_length(X,P) 在 X 中的路径 P 处定位数组并返回该数组的长度 ``` json_array_length('[1,2,3,4]') → 4 json_array_length('[1,2,3,4]', '$') → 4 json_array_length('[1,2,3,4]', '$[2]') → 0 json_array_length('{"one":[1,2,3]}') → 0 json_array_length('{"one":[1,2,3]}', '$.one') → 3 json_array_length('{"one":[1,2,3]}', '$.two') → NULL ``` ### json_type 返回类型 1. json_type() 返回的“类型”是以下 SQL 文本值之一：“null”、“true”、“false”、“integer”、“real”、“text”、“array”或“object” 示例 ``` json_type('{"a":[2,3.5,true,false,null,"x"]}') → 'object' json_type('{"a":[2,3.5,true,false,null,"x"]}','$') → 'object' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') → 'array' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') → 'integer' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') → 'real' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') → 'true' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') → 'false' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') → 'null' json_type（'{“a”：[2,3.5，true，false，null，“x”]}'，'$.a[5]'） →'text' json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') → NULL ``` ### json_quote 1. 用于将一个文本值（TEXT）转换为一个 JSON 字符串，并为它添加合适的引号。 2. 这个函数主要用于确保字符串在 JSON 表达式中是合法的，以防止 JSON 解析错误示例 ``` json_quote（3.14159） →3.14159 json_quote('verdant') → '"verdant"' json_quote('[1]') → '[1]"' json_quote（json（'[1]'）） →'[1]' json_quote('[1,') → '[1,' ``` ### json_valid 1. 参数 X 是格式正确的 JSON，则 json_valid(X,Y) 函数返回 1 2. 如果 X 格式不正确，则返回 0。 3. Y 参数是一个整数位掩码，它定义了“格式正确”的含义 ``` 0x01 → 输入是严格遵守规范 RFC-8259 JSON 的文本，没有任何扩展。 0x02 → 输入是具有上面描述的JSON5扩展的 JSON 文本。 0x04 → 输入是一个 BLOB，表面上看起来像是JSONB。 0x08 → 输入是严格符合内部JSONB格式的 BLOB 。 ``` 示例 ``` json_valid('{"x":35}') → 1 json_valid（'{x：35}'） →0 json_valid('{x:35}',6) → 1 json_valid('{"x":35') → 0 json_valid（NULL） →NULL ``` ## 示例 ### 存储json ``` CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, data TEXT -- 用于存储 JSON 数据 ); INSERT INTO users (name, data) VALUES ('Alice', '{"age": 25, "city": "New York"}'); INSERT INTO users (name, data) VALUES ('Bob', '{"age": 30, "city": "San Francisco"}'); ``` ### 查询 JSON 数据 ``` SELECT name, json_extract(data, '$.age') AS age FROM users; // outut name | age -------|----- Alice | 25 Bob | 30 ``` > $.age 是 JSON 路径表达式，其中 $ 表示根对象。 ### 更新 JSON 数据 ``` UPDATE users SET data = json_set(data, '$.age', 26) WHERE name = 'Alice'; ``` ### 添加或删除 JSON 字段 ``` // 添加 UPDATE users SET data = json_set(data, '$.email', 'alice@example.com') WHERE name = 'Alice'; // 删除 UPDATE users SET data = json_remove(data, '$.city') WHERE name = 'Bob'; ``` ### 查询嵌套的 JSON 数据 ``` INSERT INTO users (name, data) VALUES ('Charlie', '{"age": 28, "address": {"city": "Los Angeles", "zip": "90001"}}'); SELECT name, json_extract(data, '$.address.city') AS city FROM users WHERE name = 'Charlie'; // output name | city --------|--------- Charlie | Los Angeles ``` ### 聚合和过滤 JSON 数据 ``` SELECT name, json_extract(data, '$.age') AS age FROM users WHERE json_extract(data, '$.age') > 25; ``` ### 递归处理数据可处理类似组织架构等的数据 ``` WITH json_data AS ( -- 在这里定义 JSON 数据 SELECT '{ "id": 1, "name": "CEO", "users": [ { "id": 2, "name": "Manager", "users": [ { "id": 3, "name": "Developer" }, { "id": 4, "name": "Designer" } ] }, { "id": 5, "name": "HR" } ] }' AS data ), org_tree AS ( -- 递归解析 JSON 数据 SELECT json_extract(json_data.data, '$.id') AS id, json_extract(json_data.data, '$.name') AS name, json_extract(json_data.data, '$.users') AS json_data FROM json_data UNION ALL SELECT json_extract(value, '$.id') AS id, json_extract(value, '$.name') AS name, json_extract(value, '$.users') AS json_data FROM org_tree, json_each(org_tree.json_data) ) SELECT id, name,json_data FROM org_tree; // output 1 CEO [{"id":2,"name":"Manager","users":[{"id":3,"name":"Developer"},{"id":4,"name":"Designer"}]},{"id":5,"name":"HR"}] 2 Manager [{"id":3,"name":"Developer"},{"id":4,"name":"Designer"}] 5 HR 3 Developer 4 Designer ```