Postgresql12 JSON function new feature introduction to jsonpath query

Time:2020-3-31

brief introduction

In PostgreSQL 12, the JSON function document is updated, and three new methods with jsonpath query statement parameters are added:

  • jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
  • jsonb_path_match(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
  • jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

It can solve the query problem of complex and determinate structure JSON to a large extent. Compared with other sub queries, it greatly simplifies the query steps of JSON.

scene

When there is a JSON field in the database table that stores complex but regular information, query the information in JSON as a condition.

Create table statement:

create table code_system
(
    "Id"              uuid                  not null
        constraint "PK_code_system"
            primary key,
    "LastUpdateTime"  timestamp             not null,
    "Name"            text,
    "Title"           text,
    "UseContext"      jsonb
);

Insert into code \ u system ("Id", "name", "title", "usecontext", "lastupdatetime") values ('11261838-6cb6-4413-9d90-3bc9c867eec0 ',' cv04.50.005 ',' ABO blood type code table ',' code ': {"code": "DataDomain", "system": "http://mdm.com/codesystem/mdmfiltertype", "display": "data domain", "userselected": true}, "valuecodeableconcept": {"text": "", "Coding": [{"code": "blood \ u about", "system": "http://mdm.com/codesystem/mdmdatadomain", "display": "ABO blood group", "userselected: true}, {" code ":" basic \ u info "," system ":" http://mdm.com/codesystem/mdmdatadomain "," display ":" basic information of human "}]}}, {" code ":" standardtype "," system ":" http://mdm.com/codesystem/mdmfiltertype ", "Display": "standard type", "userselected": true}, "valuecodeableconcept": "{" text ":" "," coding ": [{" code ":" 3 "," system ":" http://mdm.com/codesystem/mdm-code-level "," display ":" industry standard "," userselected: true}]}, {"code": "{" code ":" usesubject "," system ":" http://mdm.com/codesystem/mdmfiltertype "," display ":" purpose "," userselected: true}, "Valuecodeableconcept": {"coding": [{"code": "02", "system": "http://mdm.com/codesystem/mdm-code-system-usage", "display": "interconnection standard", "userselected: true}]}]],'2019-11-16 13:56: 06.319739 ';
Insert into code "system (" Id "," name "," title "," usecontext "," lastupdatetime ") values ('c2059186-78ba-46d6-8dde-0b2b93550a8b ','gb / t2261.1-2003', 'gender code', '[{" code ": {" code ":" DataDomain "," system ":" http://mdm.com/codesystem/mdmfiltertype "," display ":" data domain "," userselected ": true}," valuecodeableconcept ": {" text ":" ", "Coding": [{"code": "sex", "system": "http://mdm.com/codesystem/mdmdatadomain", "display": "gender", "userselected: true}, {" code ":" basic_info "," system ":" http://mdm.com/codesystem/mdmdatadomain "," display ":" basic information of human "}]}, {" code ": {" code ":" standardtype "," system ":" http://mdm.com/codesystem/mdmfiltertype ", "Display": "standard type", "userselected": true}, "valuecodeableconcept": "{" text ":" "," coding ": [{" code ":" 2 "," system ":" http://mdm.com/codesystem/mdm-code-level "," display ":" national standard "," userselected: true}]}, {"code": "{" code ":" usesubject "," system ":" http://mdm.com/codesystem/mdmfiltertype "," display ":" purpose "," userselected: true}, "Valuecodeableconcept": {"coding": [{"code": "02", "system": "http://mdm.com/codesystem/mdm-code-system-usage", "display": "interconnection standard", "userselected: true}, {" code ":" 01 "," system ":" http://mdm.com/codesystem/mdm-code-system-usage "," display ":" hospital standard "," userselected: true}]}]],'2019-11-16 13:56:42.920166 ';
Insert into code_system ("Id", "name", "title", "usecontext", "lastupdatetime") values ('bde410df-1843-4d57-a600-417f59dd3121 ','de02.01','demographic and socioeconomic characteristics', '[{"code": "code": "DataDomain", "system": "http://mdm.com/codesystem/mdmfiltertype", "display": "data domain", "userselected": true}, "valuecodeableconcept": {"text": "", "Coding": [{"code": "data" meta "1", "system": "http://mdm.com/codesystem/mdmdatasetdatadomain", "display": "data element", "userselected: true}, {" code ":" data "meta", "system": "http://mdm.com/codesystem/mdmdatasetdatadomain", "display": "data element"}]}, {"code": "dataset", "system": "http://mdm.com/codesystem/mdmcodesystemtype", "display": "Dataset"}, "valuecodeableconcept": {"coding": [{"code": "dataset", "system": "http://mdm.com/codesystem/mdmcodesystemtype", "display": "dataset"}]}, {"code": {"code": "standardtype", "system": "http://mdm.com/codesystem/mdmfiltertype", "display": "standard type", "userselected": true}, "valuecodeableconcept": "{" text ":" "," coding ": [{" code ":" "7", "system": "http://mdm.com/codesystem/mdm-code-level", "display": "other standards", "userselected: true}]}]],'2019-11-16 13:54:18.894593 ';

Example 1 jsonb? Path? Exists

Find out that the code of the object in the JSON array of usecontext field contains the code of “standardtype”, and the line of the object in the coding array of valuecodeableconcept contains the display of “national standard”, and mark the judgment mark.

SELECT "Id", "LastUpdateTime", "Name", "Title",
Jsonb_path_exists ("usecontext", '$[*]? (@. Code. Code = = "standardtype" & & @. Valuecodeableconcept. Coding [*]. Display = = "national standard"))
FROM code_system;

Explain line 2:
Jsonb? Path? Exists calls two parameters: “usecontext” means that the field in the queried code? System is passed in as the whole queried jsonb; the second parameter is the jsonpath query statement.

Interpretation of jsonpath:

  1. $represents the root element of JSON, [] represents that this element is an array element, * represents any subscript position with wildcard characters, which is combined as the element of \ $[*] any subscript of the root array.
  2. ? means to start the query from this starting point, that is, open each object in the array as the query starting point, and use @ instead of the object after the array is opened.
  3. @. code.code query that the value of the code field under the code field in the object is all equal to “standardtype”, and @. Valuecodeableconcept. Coding [*]. Display query that the display of any object in the coding array under the valuecodeableconcept field in the object is all equal to “national standard”, and find out whether it meets the query Boolean value.

Result:

Id LastUpdateTime Name Title jsonb_path_exists
11261838-6cb6-4413-9d90-3bc9c867eec0 2019-11-16 13:56:06.319739 CV04.50.005 ABO blood group code table false
c2059186-78ba-46d6-8dde-0b2b93550a8b 2019-11-16 13:56:42.920166 GB/T2261.1-2003 Gender code true
bde410df-1843-4d57-a600-417f59dd3121 2019-11-16 13:54:18.894593 DE02.01 Demographic and socioeconomic characteristics false

Be careful:It can select the filter entry as the value of the where statement.

Example 2 jsonb path match

Find out that the code containing code in the object in the JSON array of usecontext field is “DataDomain”, and that the object in the coding array containing valuecodeableconcept contains the line with code “basic_info”, and mark it with judgment mark.

SELECT "Id", "LastUpdateTime", "Name", "Title",
       jsonb_path_match("UseContext",'$[*].code.code == $codeValue && $[*].valueCodeableConcept.coding[*].code == $codingValue',
           '{"codeValue":"DataDomain","codingValue":"basic_info"}')
FROM code_system;

Explain the third line:
The third parameter of the jsonb path match call is the variable assignment statement when there is a variable marked with & dollar; in the previous jsonpath.
The third parameter is common in the three methods.

Interpretation of jsonpath:

  1. \$[*]. Code. Code = = $codevalue means that the code value of the code with object in the root array is all equal to the variable codevalue.
  2. \$[*]. Valuecodeableconcept. Coding [*]. Code = = $codingvalue indicates that the coding array of valuecodeableconcept with object in the root array has the code of object equal to codingvalue.
  3. {“codevalue”: “DataDomain”, “codingvalue”: “basic_info”} in jsonpath, the value of codevalue is “DataDomain” and the value of codingvalue is “basic_info”.

Result:

Id LastUpdateTime Name Title jsonb_path_match
11261838-6cb6-4413-9d90-3bc9c867eec0 2019-11-16 13:56:06.319739 CV04.50.005 ABO blood group code table true
c2059186-78ba-46d6-8dde-0b2b93550a8b 2019-11-16 13:56:42.920166 GB/T2261.1-2003 Gender code true
bde410df-1843-4d57-a600-417f59dd3121 2019-11-16 13:54:18.894593 DE02.01 Demographic and socioeconomic characteristics false

Be careful:There is a difference between jsonb path match and jsonb path exists. The difference is that match does not support @? Syntax and can only be judged by writing down the root node. This also leads to different scopes of parsing comprehension when the query results of the two are wildcard * with array subscript. For example:

SELECT "Id", "LastUpdateTime", "Name", "Title",
       Jsonb_path_exists ("usecontext", '$[*]? (@. Code. Code = = "DataDomain" & & @. Valuecodeableconcept. Coding [*]. Display = = "national standard"))
FROM code_system;

SELECT "Id", "LastUpdateTime", "Name", "Title",
       Jsonb_path_match ("usecontext", '$[*]. Code. Code = = "DataDomain" & & $[*]. Valuecodeableconcept. Coding [*]. Display = = "national standard"')
FROM code_system;

The semantics of these two seemingly similar jsonb ﹣ path ﹣ exists and jsonb ﹣ path ﹣ match writing methods are actually different, of course, the execution results are also different.
If you have to use the query with array subscript wildcard * in jsonb path match, in order to ensure accuracy, it is recommended to use the exist (…) function in jsonpath to convert the @ @ syntax to the @? Syntax

Example 3 jsonb path query

Find out that there is an entry with code “usesubject” in the object in the usecontext field JSON array, and expand the coding array of valuecodeableconcept in the found object to display the display field of each object as usesubject to the final result, to indicate the usesubject owned by each entry.

WITH usesubject AS(
    SELECT "Id", "LastUpdateTime", "Name", "Title",
           jsonb_path_query("UseContext",'$[*] ? (@.code.code == "UseSubject")')->'valueCodeableConcept'->'coding' coding
    FROM code_system
)
SELECT "Id", "LastUpdateTime", "Name", "Title",jsonb_array_elements(coding)->>'display' "UseSubject"  FROM usesubject us;

Explain the third line:
Jsonb? Path? Query saves the coding array of its associated valuecodeableconcept as coding after finding the entry with usesubject.

Explain line 5:
After jsonb array elements expands the coding array to the JSON object result set, the display field value in the object is taken as the final result of usesubject.

Result:

Id LastUpdateTime Name Title UseSubject
11261838-6cb6-4413-9d90-3bc9c867eec0 2019-11-16 13:56:06.319739 CV04.50.005 ABO blood group code table Interconnection standard
c2059186-78ba-46d6-8dde-0b2b93550a8b 2019-11-16 13:56:42.920166 GB/T2261.1-2003 Gender code Interconnection standard
c2059186-78ba-46d6-8dde-0b2b93550a8b 2019-11-16 13:56:42.920166 GB/T2261.1-2003 Gender code Hospital standard

Reference material

PostgreSQL 12 – Table 9.47. JSON Processing Functions

Recommended Today

PHP Basics – String Array Operations

In our daily work, we often need to deal with some strings or arrays. Today, we have time to sort them out String operation <?php //String truncation $str = ‘Hello World!’ Substr ($STR, 0,5); // return ‘hello’ //Chinese string truncation $STR = ‘Hello, Shenzhen’; $result = mb_ Substr ($STR, 0,2); // Hello //First occurrence of […]