How to use the SQL/JSON function of PostgreSQL

This article mainly explains "how to use the SQL/JSON function of PostgreSQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use the SQL/JSON function of PostgreSQL".

PostgreSQL 12 provides SQL/JSON functions for compatibility with SQL 2016 SQL/JSON features.

These functions include:

[local]: 5432 pg12@testdb=#\ df jsonb_path* List of functions Schema | Name | Result data type | Argument data types | Type- -+- -+-pg_catalog | jsonb_path_exists | boolean | target jsonb Path jsonpath, vars jsonb DEFAULT'{}':: jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_exists_opr | boolean | jsonb, jsonpath | func pg_catalog | jsonb_path_match | boolean | target jsonb, path jsonpath, vars jsonb DEFAULT'{}':: jsonb Silent boolean DEFAULT false | func pg_catalog | jsonb_path_match_opr | boolean | jsonb, jsonpath | func pg_catalog | jsonb_path_query | SETOF jsonb | target jsonb, path jsonpath, vars jsonb DEFAULT'{}':: jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_query_array | jsonb | target jsonb Path jsonpath, vars jsonb DEFAULT'{}':: jsonb, silent boolean DEFAULT false | func pg_catalog | jsonb_path_query_first | jsonb | target jsonb, path jsonpath, vars jsonb DEFAULT'{}':: jsonb, silent boolean DEFAULT false | func (7 rows)

Simple trial:

[local]: 5432 pg12@testdb=# CREATE TABLE characters (data jsonb); "weight": 0.1}, {"name": "ring of strength", "weight": 2.4}], "arm_right": "Sword of flame", "arm_left": "Shield of faith"}') CREATE TABLETime: 208.690 ms [local]: 5432 pg12@testdb=# INSERT INTO characters VALUES ('pg12@testdb'# {"name": "Yksdargortso", pg12@testdb'# "id": 1 male, pg12@testdb'# "hp": 300 male # "level": 10 hp # "class": "warrior" Pg12@testdb'# "equipment": pg12@testdb'# {pg12@testdb'# "rings": [pg12@testdb'# {"name": "ring of despair", pg12@testdb'# "weight": 0.1pg12@testdb'#}, pg12@testdb'# {"name": "ring of strength" Pg12@testdb'# "weight": 2.4pg12@testdb'#} pg12@testdb'#], pg12@testdb'# "arm_right": "Sword of flame", pg12@testdb'# "arm_left": "Shield of faith" pg12@testdb'#} pg12@testdb'#}') INSERT 0 1Time: 3.881 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# SELECT jsonb_path_query (data,'$equipment.rings [0] .name') AS ring_name FROM characters Ring_name-"ring of despair" (1 row) Time: 10.081 ms [local]: 5432 pg12@testdb=# SELECT jsonb_path_query (data,'$equipment.rings [0]. *') AS data FROM characters Data-"ring of despair" 0.1 (2 rows) Time: 0.687 ms [local]: 5432 pg12@testdb=# SELECT jsonb_path_query (data,'$equipment.rings [*] .weight.floor ()') AS weight FROM characters; weight-0 2 (2 rows)

If it is PG 11 or below, it needs to be implemented using operators such as # > >.

Testdb=# select data# > >'{equipment,rings,0,name}'AS ring_name FROM characters Ring_name-ring of despair (1 row) Thank you for your reading, the above is the content of "how to use the SQL/JSON function of PostgreSQL". After the study of this article, I believe you have a deeper understanding of how to use the SQL/JSON function of PostgreSQL. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!