Skip to main content
This page lists operators that can be used on text values storing JSON.

​
Operator ->>

Get value of a JSON field as text. It never looks into nested JSON documents. Special characters like / in field names are escaped and not interpreted as JSON path separators.

​
Syntax

<json>->>'<field_name>'

​
Parameters

ParameterDescriptionSupported input types
<json>The JSON document.TEXT
<field_name>Name of the JSON field for which the value should be extracted.TEXT

​
Return Type

TEXT
  • If any input values are NULL, the function will return NULL.
  • If the field name is not found in the JSON document, the function returns NULL.

​
Examples

The following CREATE TABLE and INSERT statements set up the examples. 
create table json_operators (s text);

insert into json_operators
values ('
{
    "key": 123,
    "key~with~tilde": 2,
    "key/with/slash": 3,
    "value": {
      "dyid": 987,
      "keywords" : ["insanely","fast","analytics"]
    }
}
');
Example 
SELECT s->>'key' FROM json_operators;
Returns
?column? (TEXT)
‘123’
Example 
SELECT s->>'key/with/slash' FROM json_operators;
Returns
?column? (TEXT)
‘3’
There is no need to escape ~ and / in the field name when using the ->> operator. Example 
SELECT s->>'value' FROM json_operators;
Returns
?column? (TEXT)
'{\n "dyid": 987,\n "keywords" : ["insanely","fast","analytics"]\n }\n'
This example returns the nested JSON document associated to the JSON field value as a TEXT value. Example 
SELECT s->>'value'->>'dyid' FROM json_operators;
Returns
?column? (TEXT)
‘987’
The first ->> operator retrieves the nested JSON document in text form. Applying the second ->> operator on the nested JSON document returns the value associated with the JSON field dyid. The operator returns this value as a string. Example 
SELECT s->>'value/dyid' FROM json_operators;
Returns
?column? (TEXT)
NULL
The JSON document has no field called value/dyid. The ->> operator never looks into nested JSON documents.
⌘I