这篇文章主要讲解了“怎么使用PostgreSQL的SQL/JSON函数”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么使用PostgreSQL的SQL/JSON函数”吧！

PostgreSQL 12提供了SQL/JSON函数用以兼容SQL 2016 SQL/JSON特性.
这些函数包括:

[local]:5432pg12@testdb=#\dfjsonb_path*ListoffunctionsSchema|Name|Resultdatatype|Argumentdatatypes|Type------------+------------------------+------------------+-------------------------------------------------------------------------------------------+------pg_catalog|jsonb_path_exists|boolean|targetjsonb,pathjsonpath,varsjsonbDEFAULT'{}'::jsonb,silentbooleanDEFAULTfalse|funcpg_catalog|jsonb_path_exists_opr|boolean|jsonb,jsonpath|funcpg_catalog|jsonb_path_match|boolean|targetjsonb,pathjsonpath,varsjsonbDEFAULT'{}'::jsonb,silentbooleanDEFAULTfalse|funcpg_catalog|jsonb_path_match_opr|boolean|jsonb,jsonpath|funcpg_catalog|jsonb_path_query|SETOFjsonb|targetjsonb,pathjsonpath,varsjsonbDEFAULT'{}'::jsonb,silentbooleanDEFAULTfalse|funcpg_catalog|jsonb_path_query_array|jsonb|targetjsonb,pathjsonpath,varsjsonbDEFAULT'{}'::jsonb,silentbooleanDEFAULTfalse|funcpg_catalog|jsonb_path_query_first|jsonb|targetjsonb,pathjsonpath,varsjsonbDEFAULT'{}'::jsonb,silentbooleanDEFAULTfalse|func(7rows)

简单试用:

[local]:5432pg12@testdb=#CREATETABLEcharacters(datajsonb);"weight":0.1},{"name":"ringofstrength","weight":2.4}],"arm_right":"Swordofflame","arm_left":"Shieldoffaith"}}');CREATETABLETime:208.690ms[local]:5432pg12@testdb=#INSERTINTOcharactersVALUES('pg12@testdb'#{"name":"Yksdargortso",pg12@testdb'#"id":1,pg12@testdb'#"sex":"male",pg12@testdb'#"hp":300,pg12@testdb'#"level":10,pg12@testdb'#"class":"warrior",pg12@testdb'#"equipment":pg12@testdb'#{pg12@testdb'#"rings":[pg12@testdb'#{"name":"ringofdespair",pg12@testdb'#"weight":0.1pg12@testdb'#},pg12@testdb'#{"name":"ringofstrength",pg12@testdb'#"weight":2.4pg12@testdb'#}pg12@testdb'#],pg12@testdb'#"arm_right":"Swordofflame",pg12@testdb'#"arm_left":"Shieldoffaith"pg12@testdb'#}pg12@testdb'#}');INSERT01Time:3.881ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#SELECTjsonb_path_query(data,'$.equipment.rings[0].name')ASring_nameFROMcharacters;ring_name-------------------"ringofdespair"(1row)Time:10.081ms[local]:5432pg12@testdb=#SELECTjsonb_path_query(data,'$.equipment.rings[0].*')ASdataFROMcharacters;data-------------------"ringofdespair"0.1(2rows)Time:0.687ms[local]:5432pg12@testdb=#SELECTjsonb_path_query(data,'$.equipment.rings[*].weight.floor()')ASweightFROMcharacters;weight--------02(2rows)

如果是PG 11或以下版本,则需要使用#>>等操作符实现

testdb=#selectdata#>>'{equipment,rings,0,name}'ASring_nameFROMcharacters;ring_name-----------------ringofdespair(1row)

感谢各位的阅读，以上就是“怎么使用PostgreSQL的SQL/JSON函数”的内容了，经过本文的学习后，相信大家对怎么使用PostgreSQL的SQL/JSON函数这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！