postgresql json操作

-- json和jsonb共有的运算符
-- 获取JSON数组元素(索引从0开始，从末尾开始计算负整数)
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 -- {"c":"baz"}
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->-1 -- {"c":"baz"}
-- 按KEY获取JSON对象字段
select '{"a": {"b":"foo"}}'::json->'a' -- {"b":"foo"}
-- 和->唯一的区别就是获取的值是文本
select '[1,2,3]'::json->>2 -- 3
select     '{"a":1,"b":2}'::json->>'b' -- 2
-- 可以深度的获取JSON对象
select     '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' -- {"c": "foo"}
-- 可以深度的获取JSON对象作为文本
select     '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' -- 3

-- jsonb特有的运算符
-- 左侧JSON值是否包含顶级正确的JSON路径,通俗点就是我们数学上说的包含关系
select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb -- true
select '{"a":1, "b":2}'::jsonb @> '{"b":4}'::jsonb -- false
select '{"a":1, "b":2}'::jsonb @> '{"c":4}'::jsonb -- false
select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb -- true
select '{"c":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb -- false
select '["a","b","c"]'::jsonb @> '["a"]' ::jsonb -- true
-- 这个字符串是否存在顶级键
select '{"a":1, "b":2}'::jsonb ? 'b' -- true
-- 这个字符串数组是否有一个是顶级键
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] -- true
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] -- false
-- 这个字符串数组是否都是顶级键
select '["a", "b"]'::jsonb ?& array['a', 'b'] -- true
select '["a", "b"]'::jsonb ?& array['a', 'c'] -- false
-- 将两个jsonb值连接成一个新的jsonb值
select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb -- 时间过长 1.922s
-- 删除键值对
select '{"a":1, "b":2}'::jsonb - 'a'
select '{"a":1, "b":2}'::jsonb - 'b'
-- 删除具有指定索引的数组元素（从末尾开始计算负整数）。如果顶级容器不是数组，则会引发错误。
select '["a", "b"]'::jsonb - 1
-- 删除具有指定路径的字段或元素（对于JSON数组，从末尾开始计算负整数）
select '["a", {"b":1}]'::jsonb #- '{1,b}'

-- json创建函数
-- 返回值为json或jsonb
select to_json('Fred said "Hi."'::text) -- "Fred said \"Hi.\""
select to_jsonb('Fred said "Hi."'::text) -- "Fred said \"Hi.\""
-- 将数组作为JSON数组返回
select array_to_json('{{1,5},{99,100}}'::int[]) -- [[1,5],[99,100]]
select array_to_json('{{1,5},{99,100}}'::int[],true) -- [[1,5],[99,100]] 中间会有个换行符
-- 将行作为JSON对象返回,这个应该比较少用吧
select row_to_json(row(1,'foo')) -- {"f1":1,"f2":"foo"}
-- 从可变参数列表构建可能异构类型的JSON数组。
select json_build_array(1,2,'3',4,5)
select jsonb_build_array(1,2,'3',4,5) -- [1, 2, "3", 4, 5]
-- 从可变参数列表构建JSON对象。按照惯例，参数列表由交替的键和值组成。
select json_build_object('foo',1,'bar',2)
select jsonb_build_object('foo',1,'bar',2) -- {"bar": 2, "foo": 1}
-- 以下三种格式都可以转换成json对象
-- 从文本数组中构建JSON对象。数组必须具有一个具有偶数个成员的维度，在这种情况下，它们被视为交替的键/值对，或者两个维度，使得每个内部数组具有恰好两个元素，这些元素被视为键/值对。
select json_object('{a, 1, b, "def", c, 3.5}') -- {"a" : "1", "b" : "def", "c" : "3.5"}
select json_object('{{a, 1},{b, "def"},{c, 3.5}}') -- {"a" : "1", "b" : "def", "c" : "3.5"}
select json_object('{a, b}', '{1,2}') -- {"a" : "1", "b" : "2"}

-- JSON处理函数
-- 获取当前最外层json数组的长度
select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') -- 5
-- 将最外层的JSON对象扩展为一组键/值对。
select * from json_each('{"a":"foo", "b":"bar"}')
select * from json_each('{"a":"foo", "b":{"f1":1,"f2":[5,6]}}')
-- 和上面一样，只不过是text类型。
select * from json_each_text('{"a":"foo", "b":"bar"}')
-- 返回path_elems指向的JSON值（相当于＃>运算符）。
select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') -- {"f5":99,"f6":"foo"}
select json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4','f5') -- 99
-- 返回path_elems指向的JSON值作为文本（相当于＃>> 运算符）。
select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') --foo
-- 返回最外层JSON对象中的键集。
select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
-- 以下两个官方例子无法使用
select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}')
select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
-- 将JSON数组扩展为一组JSON值。
select * from json_array_elements('[1,true, [2,false]]')
select * from json_array_elements_text('["foo", "bar"]')
-- 以文本字符串形式返回最外层JSON值的类型
select json_typeof('-123.4') --number
select json_typeof('[1,true, [2,false]]') --array
select json_typeof('true') --boolean
-- 这些有点偏
select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text)
select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
-- 这个专门替换的，昨天就是用这个，效果那叫一个好，默认最后是true，无则添加
select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
-- 缩进的JSON文本返回
select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')