JSON Functions and Operators

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL JSON Functions and Operators

Introduction

Here the operators that are available for use with JSON data.

JSON Operators

Operator Description Example
->

Get JSON array element

Operand type : int

'[1,2,3]'::json->2
->

Get JSON object field

Operand type : text

'{"a":1,"b":2}'::json->'b'
->>

Get JSON array element as text

Operand type : int

'[1,2,3]'::json->>2
->>

Get JSON object field as text

Operand type : text

'{"a":1,"b":2}'::json->>'b'
#>

Get JSON object at specified path

Operand type : array of text

'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>>

Get JSON object at specified path as text

Operand type : array of text

'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

JSON Support Functions

array_to_json() function

Returns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension 1 elements if pretty_bool is true.

Syntax:

array_to_json(anyarray [, pretty_bool])

Return Type

json

Example

  1. SELECT array_to_json('{{2,8},{79,111}}'::int[]);  

Here is the result.

  array_to_json
------------------
 [[2,8],[79,111]]
(1 row)

row_to_json( ) function

Returns the row as JSON. Line feeds will be added between level 1 elements if pretty_bool is true.

Syntax:

row_to_json(record [, pretty_bool])

Return Type

json

Example

  1. SELECT row_to_json(row(1,'test'));  

Here is the result.

     row_to_json
----------------------
 {"f1":1,"f2":"test"}
(1 row)

to_json() function

Returns the value as JSON. If the data type is not built in, and there is a cast from the type to json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value, the text representation will be used, escaped and quoted so that it is legal JSON.

Syntax : not support 9.2

to_json(anyelement)

Return Type

json

Example

  1. SELECT to_json('Alex said "Sorry."'::text);  

Here is the result.

     to_json
----------------------
 "Alex said "Sorry.""
(1 row)

json_array_length() function

Returns the number of elements in the outermost JSON array.

json_array_length(json)

Return Type

int

Example

  1. SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');  

Here is the result.

     json_array_length
----------------------
 5
(1 row)

json_each() function

Expands the outermost JSON object into a set of key/value pairs.

json_each(json)

Return Type

SETOF key text, value json

Example

  1. SELECT * FROM json_each('{"a":"apple", "b":"biscuit"}');  

Here is the result

key | value
-----+-------
 a   | "apple"
 b   | "biscuit"
 

json_each_text( ) function

Expands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.

json_each_text(from_json json)

Return Type

SETOF key text, value json

Example

  1. SELECT * FROM json_each_text('{"a":"apple", "b":"biscuit"}');  

Here is the result

key | value
-----+-------
 a   | apple
 b   | biscuit

json_extract_path( ) function

Returns JSON object pointed to bypath_elems.

json_extract_path(from_json json, VARIADIC path_elems text[])

Return Type

json

Example

  1. SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');  

Here is the result

   json_extract_path
----------------------
{"f5":99,"f6":"foo"}

json_extract_path_text( ) function

Returns JSON object pointed to by path_elems.

json_extract_path_text(from_json json, VARIADIC path_elems text[])

Return Type

text

Example

  1. SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4''f6');  

Here is the result

   json_extract_path_text
---------------------------
   foo

json_object_keys() function

Returns set of keys in the JSON object. Only the"outer" object will be displayed.

json_object_keys(json)

Return Type

SETOF text

Example

  1. SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');  

Here is the result

json_object_keys
------------------
 f1
 f2

json_populate_record() function

Expands the object infrom_json to a row whose columns match the record type defined by the base. The conversion will be the best effort; columns in a base with no corresponding key in from_jsonwill be left null. If a column is specified more than once, the last value is used.

json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]

Return Type

anyelement

Example

  1. SELECT * FROM json_populate_record(null::x, '{"a":1,"b":2}');  

Here is the result

 a | b
---+---
 1 | 2

json_populate_recordset() function

Expands the outermost set of objects infrom_json to a set whose columns match the record type defined by the base. The conversion will be the best effort; columns in a base with no corresponding key in from_jsonwill be left null. If a column is specified more than once, the last value is used.

json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]

Return Type

SETOF anyelement

Example

  1. SELECT * FROMjson_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]');  

Here is the result.

 a | b
---+---
 1 | 2
 3 | 4

json_array_elements() function

Expands a JSON array to a set of JSON elements.

json_array_elements(json)

Return Type

SETOF json

Example

  1. SELECT json_array_elements('[1,true, [2,false]]');  

Here is the result.

 value
-----------
 1
 true
 [2,false]