Introduction to json and jsonn Operators of PostgresSQL

There are times where we require to operate on json or jsonb String(a json stored as string in the database).To do so postgresSQL provided json and jsonb operators. Let us see how to use them.

JSON operators:

1. "->" 

It provides the json array element indexed from 0.The right hand operand type is "integer"
Example- SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 
Result - {"c":"baz"}

2. "->" 

It provides the json object field by key.The right hand operand type is "text"
Example- SELECT '{"a": {"b":"foo"}}'::json->'a' 
Result - {"b":"foo"}

3. "->>" 

It provides the json array element as text.The right hand operand type is "text"
Example- '{"a":1,"b":2}'::json->>'b'
Result - 2

4. "->>" 

It provides the json object field as text.The right hand operand type is "int"
Example- SELECT '[1,2,3]'::json->>2 
Result - 3

5. "#>" 

It provides the json object field at specified path as text.The right hand operand type is "text[]"
Example- SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 
Result - {"c": "foo"}

6. "#>>" 

It provides the json object field at specified path.The right hand operand type is "text[]"
Example- SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
Result - 3

JSONB operators

1. "@>" 

It returns the boolean value specifying whether the right json value contains within the left value. The right hand operand type is "jsonb"
Example- SELECT '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb2 
Result - true

2. "<@" 

It returns the boolean value specifying whether the left json value contains within the right value. The right hand operand type is "jsonb.
Example- SELECT '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
Result - true

3. "?" 

It returns the boolean value specifying whether the key element string exists within the Json value. The right-hand operand type is "text".
Example- SELECT '{"a":1, "b":2}'::jsonb ? 'b'
Result - true

4. "?|" 
.
It returns the boolean value specifying whether the key strings exist. The right-hand operand type is "text[]".
Example- SELECT '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
Result - true

5. "?&" 

It returns the boolean value specifying whether any of these key element string exists.The right hand operand type is "text[]".
Example- true

This is the basic introduction of how to use of "json" and "jsonb" operators of "postgresSQL".