Table of Contents

• JSON Operators
• Example Table
• JSON Query Examples
  • Retrieve the name Property
  • Retrieve the size.length Property
  • Retrieve Records with size.isBig = true
  • Retrieve Objects with id = 2 from the array

Official Documentation: PostgreSQL JSON Functions

JSON Operators

Operator	Right Operand Type	Description	Example
->	int	Get JSON array element	'[1,2,3]'::json->2
->	text	Get JSON object field	'{"a":1,"b":2}'::json->'b'
->>	int	Get JSON array element as text	'[1,2,3]'::json->>2
->>	text	Get JSON object field as text	'{"a":1,"b":2}'::json->>'b'
#>	array of text	Get JSON object at specified path	'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>>	array of text	Get JSON object at specified path as text	'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

Example Table

Id	Content
1	{ "name": "my json data 1", "size": { "length": 100, "width": 200, "isBig": false }, "array": [{ "id": 1 }, { "id": 2 }, { "id": 3 }] }
2	{ "name": "my json data 2", "size": { "length": 100, "width": 200, "isBig": true }, "array": [{ "id": 1 }, { "id": 2 }, { "id": 3 }] }

JSON Query Examples

1. Retrieve the name Property

SELECT "content" ->> 'name' FROM "example";

2. Retrieve the size.length Property

SELECT "content" -> 'size' ->> 'length' FROM "example";

3. Retrieve Records with size.isBig = true

SELECT * FROM "example" WHERE ("content" -> 'size' ->> 'isBig')::bool IS TRUE;

4. Retrieve Objects with id = 2 from the array

SELECT * FROM "example", json_array_elements("content" -> 'array') a 
WHERE (a ->> 'id')::int = 2;