PostgreSQL 9.3 new feature preview-JSON operation

Postgres 9.3 is currently under intensive development. A new feature worth noting in this version is the JSON data type. After reading the new functions for data generation article, let's take a look at the description of the new JSON feature in the commit record:

Commit a570c98d7fa0841f17bbf51d62d02d9e493c7fcc
Author: Andrew Dunstan
Date: Fri Mar 29 14:12:13 2013-0400
　
Add new JSON processing functions and parser API.
　
The JSON parser is converted into a recursive descent parser, and
Exposed for use by other modules such as extensions. The API provides
Hooks for all the significant parser event such as the beginning and end
Of objects and arrays, and providing functions to handle these hooks
Allows for fairly simple construction of a wide variety of JSON
Processing functions. A set of new basic processing functions and
Operators is also added, which use this API, including operations
Extract array elements, object fields, get the length of arrays and
Set of keys of a field, deconstruct an object into a set of key/value
Pairs, and create records from JSON objects and arrays of objects.
　
Catalog version bumped.
　
Andrew Dunstan, with some documentation ance from Merlin Moncure.

Based on the stored JSON data, this submission also introduces new APIs, operators, and functions to operate JSON data. There are 4 Operators and 8 new functions in total, this article only briefly introduces four new operators.

The following datasets are used for all experiments in the document:

postgres=# CREATE TABLE aa (a int, b json);CREATE TABLEpostgres=# INSERT INTO aa VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}');INSERT 0 1postgres=# INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');INSERT 0 1postgres=# INSERT INTO aa VALUES (3, '{"f1":[1,"Robert \"M\"",true],"f2":[2,"Kevin \"K\"",false]}');INSERT 0 1

 

The first operator is "->", which is used to directly obtain the field value from the JSON database. The text value is used to mark the field key:

postgres=# SELECT b->'f1' AS f1, b->'f3' AS f3 FROM aa WHERE a = 1;f1 | f3----+--------------------1 | "Hi I'm \"Daisy\""(1 row)

You can also use multiple keys to obtain data or a subset of data:

postgres=# SELECT b->'f1'->'f12' AS f12 FROM aa WHERE a = 2;f12-----12(1 row)postgres=# SELECT b->'f1' AS f1 FROM aa WHERE a = 2;f1---------------------{"f11":11,"f12":12}(1 row)

Another more interesting method is that when an integer is used as the key, you can directly obtain data from the stored array:

postgres=# SELECT b->'f1'->0 as f1_0 FROM aa WHERE a = 3;f1_0------1(1 row)

The second operator is "->". Unlike "->", this operator returns the specified text, and "->" returns the plain text.

postgres=# SELECT b->>'f3' AS f1 FROM aa WHERE a = 1;f1----------------Hi I'm "Daisy"(1 row)postgres=# SELECT b->'f3' AS f1 FROM aa WHERE a = 1;f1--------------------"Hi I'm \"Daisy\""(1 row)

Like "->", "->" can also be used as an integer or text key, and the position of the table in the array in the integer era is used:

postgres=# SELECT b->'f1'->>1 as f1_0 FROM aa WHERE a = 3;f1_0------------Robert "M"(1 row)postgres=# SELECT b->'f1'->1 as f1_0 FROM aa WHERE a = 3;f1_0----------------"Robert \"M\""(1 row)

Of course, you cannot use the field name to obtain data in the data:

postgres=# SELECT b->'f1'->>'1' as f1_0 FROM aa WHERE a = 3;ERROR: cannot extract field from a non-object

Similarly, you cannot use element values to obtain the same fields:

postgres=# SELECT b->1 as f1_0 FROM aa WHERE a = 3;ERROR: cannot extract array element from a non-array

The last two operators are "#>" and "#> ". used to directly obtain the elements in the array without using the first two methods "column-> '$ FIELD'-> $ INT_INDEX. this makes your query more readable.

postgres=# SELECT b#>'{f1,1}' as f1_0 FROM aa WHERE a = 3;f1_0----------------"Robert \"M\""(1 row)postgres=# SELECT b#>>'{f1,1}' as f1_0 FROM aa WHERE a = 3;f1_0------------Robert "M"(1 row)

"#>" Retrieves text data in valid JSON format, while "#>" returns plain text.

 

To sum up, these new operators greatly facilitate the operations of many applications on JSON data.

Via otacoo