-- 右边传入整数（针对纯数组），获取数组的第n个元素，n从0开始算，返回值为json
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2
-- 右边传入键值（针对关联数组），获取数组的第n个元素，n从0开始算，返回值为json
select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'c'
-- 获取json子对象，传入数组，返回json
select '{"a": {"b":{"c": "foo"}}}'::json#> '{a,b,c}'
-- json数据被分离成三条记录，这时我们就可以对其进行查询操作
select * from json_array_elements ('
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"},
{"id": "111a13d3-0225-4431-b858-678c3cfea999", "weight": "3", "quantity": "11"}
]
') as t where t::json->>'weight'='1'
-- 第二个参数表示获取键为quantity 下 max的值
select json_extract_path ('
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
','quantity','max')
--等价于上一个
select ('
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
') ::json #> '{quantity,max}'
--组合使用 查询goods 下的 id和quantity 并且加条件
select jae::json->>'id' as id, jae::json->>'quantity' as quantity from json_array_elements (
json_extract_path ('
{
"goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "weight": "1", "quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "weight": "2", "quantity": "33"}
],
"quantity": {"max": "150", "min": "2"}
}
' , 'goods' ) ) as jae where jae::json->> 'weight' = '2'
