/*
-- 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",
    "additionalProperties": {
        "type": "object",
        "properties": {
            "name" : {"type": "string"},
            "choices": { 
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "code": {"type": "string"},
                        "score": {"type": "integer"}
                         },
                    "additionalProperties": false,
                    "required": ["code", "score"]
                }
            }
        },
        "required": ["choices", "name"],
        "additionalProperties": false
    }
}
*/

			 
WITH Applicants AS
  (SELECT '{ "a1": {"name": "Andersson", 
                    "choices": [ {"code":"MPSOF","score":750},
                                 {"code":"MPALG","score":750},
                                 {"code":"MPCSN","score":800}
                               ]
                   },
            "a2": {"name": "Jonsson", 
                   "choices": [ {"code":"MPALG","score":700}]
                  },
            "a3": {"name": "Larsson", 
                   "choices": [ {"code":"MPCSN","score":850},
                                {"code":"MPALG","score":850}
                              ]
                  }
          }' :: JSONB AS val)
SELECT jsonb_path_query (val,
                         'strict $')  -- here you paste your query
FROM Applicants;
