BEGIN;
    
DROP TABLE IF EXISTS person CASCADE;
CREATE TABLE person(val json);
INSERT INTO person VALUES
('
{ 
  "name": "Mary Smith",
  "address": {
    "street": "Streetroad",
    "number": "5",
    "zipcode": "1234",
    "city": "Oslo",
    "country": "Norway"
  },
  "phoneNumbers": [
    {
      "type": "mobile",
      "number": "12345678"
    },
    {
      "type": "office",
      "number": "98765432"
    }
  ]
}
'),
('
{
  "name": "Carl Green",
  "address": {
    "street": "Streetroad",
    "number": "3",
    "zipcode": "1234",
    "city": "Oslo",
    "country": "Norway"
  },
  "phoneNumbers": [
    {
      "type": "mobile",
      "number": "34567890"
    },
    {
      "type": "office",
      "number": "76543210"
    }
  ]
}
'),
('
{
  "name": "Zoe Moore",
  "address": {
    "street": "Roadway",
    "number": "18",
    "zipcode": "9978",
    "city": "Bergen",
    "country": "Norway"
  },
  "phoneNumbers": [
    {
      "type": "mobile",
      "number": "92837465"
    },
    {
      "type": "office",
      "number": "56473829"
    }
  ]
}
');

COMMIT;

-- Find name and all phone numbers to people in Oslo
WITH
  oslo(name, num) AS (
    SELECT val->>'name', json_array_elements(val->'phoneNumbers') AS num
    FROM person
    WHERE val->'address'->>'city' = 'Oslo'
  )
SELECT name, num->>'type' AS type, num->>'number' AS number
FROM oslo;

