JSON Functions

1min

To help you query JSON data, Oxla provides some functions that will be used to operate and manipulate the JSON data. The functions are as follows:
Functions
Description
﻿JSON_EXTRACT_PATH()﻿﻿﻿
It extracts JSON sub-object at the specified path.
﻿JSON_EXTRACT_PATH_TEXT()﻿
It returns text referenced by a series of path elements in a JSON string or JSON body.
﻿JSON_ARRAY_LENGTH()﻿
It returns the number of elements in the outer array of a JSON string or JSON body.
﻿JSON_ARRAY_EXTRACT()﻿
It returns the JSON array as a set of JSON values. 
Operators are used to specify conditions when using JSON functions. Oxla also supports JSON operators as listed below:
Operators
Description
Example
->
It gets & returns the element of the JSON array.
'[{"a":"cab"},{"b":"bac"},{"c":"abc"}]'::json -> 2
->
It gets & returns the JSON object field.
'{"a": {"b":"abc"}}'::json -> 'a'
->>
It gets & returns the element of the JSON array as text.
'[11,22,33]'::json ->> 2
->>
It gets & returns the JSON object field as text.
'{"a":13,"b":33}'::json ->> 'b'
﻿

Updated 09 Oct 2023

Did this page help you?

IS NOT DISTINCT FROM Operator

JSON_EXTRACT_PATH

Functions	Description
JSON_EXTRACT_PATH()	It extracts JSON sub-object at the specified path.
JSON_EXTRACT_PATH_TEXT()	It returns text referenced by a series of path elements in a JSON string or JSON body.
JSON_ARRAY_LENGTH()	It returns the number of elements in the outer array of a JSON string or JSON body.
JSON_ARRAY_EXTRACT()	It returns the JSON array as a set of JSON values.

Operators	Description	Example
->	It gets & returns the element of the JSON array.	'[{"a":"cab"},{"b":"bac"},{"c":"abc"}]'::json -> 2
->	It gets & returns the JSON object field.	'{"a": {"b":"abc"}}'::json -> 'a'
->>	It gets & returns the element of the JSON array as text.	'[11,22,33]'::json ->> 2
->>	It gets & returns the JSON object field as text.	'{"a":13,"b":33}'::json ->> 'b'