About Pgsql JSON and JSONB data query operations Note finishing

Data processing notes on JSON and JSONB for Pgsql

1. JSON and JSONB differences
There is no difference from the point of view of user operation, the difference is mainly the system processing (preprocessing) and time-consuming differences between storage and reading. JSON writes fast, reads slowly, Jsonb writes slowly, reads fast.

2. Commonly used operators

Operator:

///Right Pass in integer (for pure array), get nth element of array, n count from 0, return value to JSON

Example: Select ' [{"A": "foo"},{"B": "Bar"},{"C": "Baz"}] ':: json->2//output {"c": "Baz"}

///Right Pass in key value (for associative array), get nth element of array, n from 0, return value as JSON

Example: Select ' {"a": {"B": "foo"}, "C": {"a": "AAA"}} '::json-> ' a '//output {"B": "Foo"}

->>//Right Pass in integer (for pure array), get nth element of array, n count from 0, return value as text

Example: Select ' [{"A": "foo"},{"B": "Bar"},{"C": "Baz"}] ':: json->>2//output {"c": "Baz"}

->>//Right Pass in key value (for associative array), get nth element of array, n count from 0, return value is text

Example: Select ' {"a": {"B": "foo"}, "C": {"a": "AAA"}} '::json->> ' a '//output {"B": "Foo"}

#>//Get JSON sub-object, pass in array, return JSON

Example: Select ' {"a": {"B": {"C": "foo"}} '::json#> ' {b} '//output {"c": "foo"}

#>>//Get JSON sub-object and convert to text, pass in array, return text

Example: Select ' {"a": {"B": {"C": "foo"}} '::json#>> ' {b} '//output {"c": "foo"}

3. Operation function
Currently, the Pgsql version provides two sets of functions, which can be generic and similar, such as Json_each and Jsonb_each, Json_array_elements and jsonb_array_elements.

JSON-related processing functions are more, commonly used in the following three, these three basic enough

Json_object_keys // returns the JSON key (multilayer only returns the first layer), and the function cannot be used for a pure array.

Json_array_elements //extract convert pure array elements

Json_extract_path // Returns a key element (equivalent to the #> operator) that the JSON value points to, and the function cannot manipulate the pure array directly.

It is important to note that if you create a field with JSON, you use JSON-related functions, and if you create a field with JSONB, you use the jsonb correlation function.

Json_object_keys Function Example:

Select Json_object_keys ('
{
"Goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "Weight": "1", "Quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "Weight": "2", "Quantity": "33"}
],
"Quantity": "10"
}
‘)

Output:

Json_object_keys Function Example:

Select Json_object_keys ('
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "Weight": "1", "Quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "Weight": "2", "Quantity": "33"}
],
‘)

Output:

Error:cannot call Json_object_keys in an array//cannot be used for arrays

Json_array_elements Function Example:

Select 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"}
]
‘)

We see that the JSON data is separated into three records, and then we can query it.

For example, whether the query contains weight=3 data.

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 Jae
where jae::jsonb->> ' weight ' = ' 3 '

#输出:

We see this so that we can query inside the JSON data.

Json_extract_path Function Example:

For example, to get the value of the key ' goods ':

{
"Goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "Weight": "1", "Quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "Weight": "2", "Quantity": "33"}
],
"Quantity": {"Max": "$", "Min": "2"}
}

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": "$", "Min": "2"}
}
', ' goods '); The second parameter represents a value that gets the key to goods

#输出:

The Json_extract_path function is the same as the operator provided by Pgsql #>.

Select ('
{
"Goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "Weight": "1", "Quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "Weight": "2", "Quantity": "33"}
],
"Quantity": {"Max": "$", "Min": "2"}
}
'):: JSON #> ' {goods} '

The output of the two is consistent.

Also we want to output the key quantity the value of the next key 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": "$", "Min": "2"}
}
', ' Quantity ', ' Max ');

-OR

Select ('
{
"Goods":
[
{"id": "676a13d3-0225-4431-b858-678c3cfeab74", "Weight": "1", "Quantity": "9999999"},
{"id": "111a13d3-0225-4431-b858-678c3cfeab75", "Weight": "2", "Quantity": "33"}
],
"Quantity": {"Max": "$", "Min": "2"}
}
'):: Json #> ' {quantity, max} '

Both outputs are the same.

These functions can be used in conjunction.

For example, we want to query the key "goods" under the weight =2 ID and Quantity value, the statement is as follows:

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": "$", "Min": "2"}
}
', ' goods ')) as Jae where jae::json->> ' weight ' = ' 2 '

#输出:

The JSON statement above can be used as a field, which is equivalent to manipulating the table record.

Next we'll explain the use of JSON in a table in the same example:

Example: Query the table in the Jsonb_msg field under goods id=1003 and 1002 Records, enter the table as:

SQL query statement:

Select name,* from Upgrade_test.test1 test
where
(SELECT COUNT (*) from jsonb_array_elements (
Jsonb_extract_path (test.json_msg, ' goods ')) as Jae where jae::json->> ' id ' in (' 1001 ', ' 1003 ')) > 0;

#输出:

Efficiency is OK:

Total Query Runtime:11 msec
The 2 rows were retrieved.

Official Documents page:

Https://www.postgresql.org/docs/9.4/static/functions-json.html

About Pgsql JSON and JSONB data query operations Note finishing