/*
-- psql -v ON_ERROR_STOP=1 -U postgres portal

-- Deletes everything
\c portal
\set QUIT true
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
\set QUIET false
*/


/* Schema

{"type": "object",
 "oneOf": [{"$ref": "#/definitions/leaf"},
           {"$ref": "#/definitions/tree"}],
 "definitions": {
    "leaf": {
      "type": "object",
      "properties": {"value": {"type": "integer"}},
      "required": ["value"],
      "additonalProperties": false},
    "tree": {
      "type": "object",
      "properties": {
        "value": {"type": "integer"},
        "left": {"$ref": "#"},
        "right": {"$ref": "#"}},
      "required": ["value","left","right"],
      "additonalProperties": false}
 }
}
*/


WITH BTKeyValue AS
  (SELECT '{ "value":3,
             "left": { "value":1,
                       "left": {"value":0},
                       "right": {"value":2}
                     },
             "right": { "value":5,
                        "left": {"value":4},
                        "right": {"value":6}
                     }
           }' :: JSONB AS val)
SELECT jsonb_path_query (val,
                         'strict $')  -- here you paste your query
FROM BTKeyValue;          -- like this it will return the whole object


/* Schema

{ "type": "object",
  "properties": {
    "value": {"type": "integer"},
    "children": {"type": "array",
                 "items" : {"$ref": "#"},
                 "minItems":2,
                 "maxItems":2}},
  "required": ["value"],
  "additionalProperties": false
}
*/


WITH BTArrays AS
  (SELECT '{ "value":3,
             "children": [ {"value":1,
                            "children": [{"value":0},{"value":2}]}, 
                           {"value":5,
                            "children": [{"value":4},{"value":6}]}
                         ]
           }' :: JSONB AS val)
SELECT jsonb_path_query (val,
                         'strict $')  -- here you paste your query
FROM BTArrays;            -- like this it will return the whole object
