笔记：关于数据库中的 Json 类型

-- 定义一个json类型的数据，如下：
select '["item0", "item1", "item2"]'::json;                       -- 结果：["item0", "item1", "item2"]

-- json类型有两种操作符，分别为->和->>，如下：
select '["item0", "item1", "item2"]'::json->0;                    -- 结果："item0"
select '["item0", "item1", "item2"]'::json->>0;                   -- 结果：item0

-- 初步得出结论：
-- 1. 操作符后面跟上数字，可以获取json数组对应下标的元素
-- 2. ->提取数据时，会原封不动地保留数据原来的样子
-- 3. ->>则直接将数据解释成字符串

-- 对于第2和3点，我们这里对其进行验证：
select '["item0", "item1", "item2"]'::json->0 = 'item0';          -- 失败：No operator matches the given name and argument type(s).
select '["item0", "item1", "item2"]'::json->>0 = 'item0';         -- 结果：t(true)

-- 这两个操作符也可以用来提取json对象中某个key对应的值，如下：
select '{"name": "zhangsan", "age": 18}'::json->'age';            -- 结果：18
select '{"name": "zhangsan", "age": 18}'::json->>'age';           -- 结果：18

-- 注意，上面的json中，虽然"age"对应的值是数字18，但->>得到的依旧是字符串，比如：
select '{"name": "zhangsan", "age": 18}'::json->>'age' = 18;      -- 失败：No operator matches the given name and argument type(s).
select '{"name": "zhangsan", "age": 18}'::json->>'age' = '18';    -- 结果：t(true)

-- 也可以链式调用，如：
select '[{"name": "zhangsan", "age": 18}]'::json->0->'name';      -- 结果："zhangsan"
select '[{"name": "zhangsan", "age": 18}]'::json->0->>'name';     -- 结果：zhangsan

-- 注意，链式调用时，最后一个操作符之前的所有操作符必须是->
-- 这是因为->>返回的是字符串，而字符串是不能被json操作符操作的
-- 说到这里，我们也可以完善一下第2点结论了：
-- 2. ->提取数据时，返回的数据依旧是json类型

-- 对于链式调用，我们还可以使用#>和#>>操作符来简化：
select '[{"name": "zhangsan", "age": 18}]'::json#>'{0, "name"}';  -- select '[{"name": "zhangsan", "age": 18}]'::json->0->'name';
select '[{"name": "zhangsan", "age": 18}]'::json#>>'{0, "name"}'; -- select '[{"name": "zhangsan", "age": 18}]'::json->0->>'name';

1.2. 额外操作

1.3 实战

-- 假如有一张form_data表，它有metas字段和remark字段：
-- 1. metas字段是jsonb类型，是一个json数组，数组中每个元素就代表一个表单项；表单项有name/type/placeholder等属性
-- 2. remark是字符串类型，但实际存储的是json串，其中包含creator等属性

-- 查询张三创建的表单；可以在查询时，将remark字段转成json类型，然后利用json操作符来进行条件判断
SELECT * FROM form_data WHERE remark::json->>'creator' = '张三';

-- 查询第一个表单项的名称包含"申请人"关键字的表单
SELECT * FROM form_data WHERE metas->0->>'name' LIKE '%申请人%';

1.4 扩展

-- 查询请假审批工作流及其表单，其中f.metas字段是json类型的，并且存的是数组
-- 执行该语句，由于f.metas数据很多，因此表单数据很难直观展示出来
SELECT w.workflow_key, w.name workflow_name, f.metas 
FROM workflow w JOIN form_data f 
ON w.form_key = f.key AND w.is_del = 0 AND f.is_del = 0 AND w.name = '请假审批';

-- 可以使用jsonb_to_recordset()函数将json转成一个表，如下所示
-- 该函数会自动将src表的metas字段转成临时表form_item，然后将src表和form_item表连接起来
SELECT src.workflow_key, src.workflow_name, form_item.name, form_item.key from (
    SELECT w.workflow_key, w.name workflow_name, f.metas 
    FROM workflow w JOIN form_data f 
    ON w.form_key = f.key AND w.is_del = 0 AND f.is_del = 0 AND w.name = '请假审批'
) src, jsonb_to_recordset(metas) AS form_item(key TEXT, name TEXT);

-- 与jsonb_to_recordset()类似的函数还有jsonb_to_record()函数，前者用于转换json数组，后者用于转换json对象