PostgreSQL 操作符
->
->>#>
#>>
说明:
1、-> 运算符用于通过 key 获取json或jsonb对象字段的值,返回值类型为json或jsonb2、->> 运算符用于通过 key 获取json或jsonb对象字段的值,返回值类型为text
3、#>运算符用于通过指定提取key的顺序,获取指定的值,返回类型为json或jsonb
4、#>>运算符用于通过指定提取key的顺序,获取指定的值,返回值类型为text
示例说明
表名:test_t
json类型的字段名:test_json
字段值:{"a1":"b1","a2":{"c1":"d1","c2":"d2"}}
获取json或jsonb中的某个key对应的值
SELECT test_json -> 'a1' AS tv FROM test_t;
SELECT test_json ->> 'a1' FROM test_t;
SELECT test_json #> '{a2,c1}' FROM test_t;
SELECT test_json #>> '{a2,c1}' FROM test_t;
-> 和->> 组合查询复杂的json对象
SELECT test_json -> 'a2'->>'c2'
FROM test_t;
PostgreSQL 提供的JSON 处理函数
json_each
json_each ( json ) → setof record ( key text , value json )jsonb_each ( jsonb ) → setof record ( key text , value jsonb )功能:key,value以记录的形式返回,value类型为json或jsonb示例: select * from json_each ((select test_json AS tv from test_t ));返回:
json_each_text
json_each_text ( json ) → setof record ( key text , value text )jsonb_each_text ( jsonb ) → setof record ( key text , value text )功能:key,value以记录的形式返回,value类型为text示例:select * from json_each_text ((select test_json AS tv from test_t ));返回:
json_extract_path
json_extract_path ( from_json json , VARIADIC path_elems text[] ) → jsonjsonb_extract_path ( from_json jsonb , VARIADIC path_elems text[] ) → jsonb功能:按照指定的key顺序,查找值,值的类型为json或jsonb。类似#>示例:select * from json_extract_path((select test_json AS tv from test_t),'a2', 'c1');返回:
json_extract_path_text
json_extract_path_text ( from_json json , VARIADIC path_elems text[] ) → textjsonb_extract_path_text ( from_json jsonb , VARIADIC path_elems text[] ) → text功能:按照指定的key顺序,查找值,值的类型为text。类似#>>
示例:select * from json_extract_path_text ((select test_json AS tv from test_t),'a2', 'c1');返回
json_object_keys
json_object_keys ( json ) → setof textjsonb_object_keys ( jsonb ) → setof text功能:返回json对象的key示例:select * from json_object_keys ((select test_json AS tv from test_t));返回