/*
-- 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": "array",
  "items": {"$ref": "#/definitions/categories"},
  "definitions": {
    "categories": {
      "type": "object",
      "properties": {
        "category": {"type": "string"},
        "products": {
          "type": "array",
          "items": {"$ref": "#/definitions/product"}
        }
      },
      "required": ["category","products"],
      "additionalProperties": false
    },
    "product": {
      "type": "object",
      "properties": {
        "manufacturer": {"type": "string"},
        "model": {"type": "string"},
        "price": {"type": "integer"},
        "discount": {"type": "number"}
      },
      "required": ["manufacturer","model","price","discount"],
      "additionalProperties": false
    }
  }
}
*/


WITH Inventory AS
  (SELECT '[ {"category": "GPUs",
              "products": [ {"manufacturer": "Nvidia",
                             "model": "3070GTX",
                             "price": 11890,
                             "discount": 0.05
                            },
                            {"manufacturer": "AMD",
                             "model": "RX6800",
                             "price": 13490,
                             "discount": 0.10
                            }
                          ]
             },
             {"category": "CPU",
              "products": [ {"manufacturer": "Intel",
                             "model": "i7-12700k",
                             "price": 4590,
                             "discount": 0.3
                            },
                            {"manufacturer": "AMD",
                             "model": "Ryzen 5600X",
                             "price": 2739,
                             "discount": 0.12
                            }
                          ]
             },
             {"category": "Memory",
              "products": [ {"manufacturer": "Corsair",
                             "model": "LPX 32GB",
                             "price": 4590,
                             "discount": 0.15
                             },
                             {"manufacturer": "Kingston",
                              "model": "Fury 64GB",
                              "price": 2999,
                              "discount": 0.20
                             },
                             {"manufacturer": "Kingston",
                              "model": "Fury 128GB",
                              "price": 4999,
                              "discount": 0.10
                             }
                           ]
             }
           ]' :: JSONB AS val)
SELECT jsonb_path_query (val,
                         'strict $')  -- here you paste your query
FROM Inventory;
