Postgresql & json: JSON Functions and Operators

Trabla: Postgresql & json: JSON Functions and Operators

Solving:

PostgreSQL 9.3+

JSON Operators

1. ->  Get JSON array element




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

2. ->  Get JSON array element

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


3. ->> Get JSON array element as text

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

4. ->> Get JSON object field as text

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


5. #> Get JSON object at specified path

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


6.  #>> Get JSON object at specified path as text

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


 Examples:

CREATE TABLE tbl_car (  id int PRIMARY KEY, params text );
INSERT INTO tbl_car( id, params ) VALUES ( 1, '{"vendor":"BMW","model":"M6"}');
SELECT  id, params::json->>'model' FROM tbl_car;
---------------------------
1 | M6