PostgreSQL 9.4: Introduced JSON Functions and Operators (Part 2/3)

In the previous post, I have discussed basic about newly introduced JSON data type of PostgreSQL 9.4.

In this post, I am showing some of the basic JSON functions and operator which are also introduced by PostgreSQL 9.4.

First, Create sample table with jsonb data type and insert sample JSON data:

SELECT JSON Data using ‘->’ and ‘->>’ operators:

JSON Key value criteria in WHERE clause:

JSON_EACH and JSONB_EACH Functions:
JSON_EACH and JSONB_EACH function to expand a JSONs data and nested JSONs are not expanded.
JSON_EACH for json data type and JSONB_EACH for jsonb data type.

JSON_OBJECT_KEYS and JSONB_OBJECT_KEYS Functions:
Returns all set of keys of JSON object and nested keys are not displayed.
JSON_OBJECT_KEYS is for json data type and JSONB_OBJECT_KEYS is for jsonb data type.

JSON_TYPEOF and JSONB_TYPEOF Functions:
This both functions respectively, for each data type and return the data type of the JSON keys.

For more reference you can visit this official PostgreSQL document.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz