Postgres with JSON

create objects JSON

select '{}'::json;

create objects with JSON data

select '{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}'::json;

return json (->)

select '{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}'::json->'hobby';

return text (->>)

select '{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}'::json->>'hobby';

return nested json data (-> and ->>)

select '{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}'::json->'address'->>'country';

return nested json data in int (cast data type)

select ('{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}'::json->>'height')::int;

get element from json array

select '{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}'::json->'hobby'->>0;

create table with json data

create table people (
  id serial primary key,
  metadata jsonb
)


insert into people(metadata) values('{
  "name": "Rocky",
  "height": 153,
  "hobby": ["watching", "coffee"],
  "address": {
    "country": "Cambodia",
    "city": "Phnom Penh"
  }
}')

select * from people;

update json data

insert into
  people (metadata)
values
  (
    '{
  "name": "John",
  "hobby": ["basketball", "synthesizers", "coffee"],
  "address": {
    "country": "Australia"
  }
}'
  ),
  (
    '{
  "name": "Thor",
  "height": 193,
  "hobby": ["kayaking","travelling", "coffee"],
  "address": {
    "country": "Singapore"
  }
}'
  )

filter json data

select metadata->>'name' name, metadata->'address'->>'country' country from people
where (metadata->>'height')::int<180

filter json data with contain properly

select metadata->>'name' name, metadata->'address'->>'country' country from people
where metadata ? 'height'

for array contains all

select
  metadata ->> 'name' name,
  metadata -> 'address' ->> 'country' country,
  metadata->'hobby' hobby
from
  people
where
  metadata -> 'hobby' ?& '{"watching", "coffee"}'

for array contains any

select
  metadata ->> 'name' name,
  metadata -> 'address' ->> 'country' country,
  metadata->'hobby' hobby
from
  people
where
  metadata -> 'hobby' ?| '{"watching", "coffee"}'

filter json data

select
  metadata ->> 'name' name,
  metadata -> 'address' ->> 'country' country,
  metadata->'hobby' hobby
from
  people
where
  metadata @> '{"name": "Rocky"}'

filter json data with array property

select
  metadata ->> 'name' name,
  metadata -> 'address' ->> 'country' country,
  metadata->'hobby' hobby
from
  people
where
  metadata @> '{"hobby": ["coffee"]}'

filter json data with array and objects property

select
  metadata ->> 'name' name,
  metadata -> 'address' ->> 'country' country,
  metadata->'hobby' hobby
from
  people
where
  metadata @> '{"hobby": ["coffee"], "address": {"country":"Cambodia"}}'

query data

create table books (
  id serial primary key,
  title text,
  author text,
  metadata jsonb
);

insert into books
  (title, author, metadata)
values
  (
    'The Poky Little Puppy',
    'Janette Sebring Lowery',
    '{"description":"Puppy is slower than other, bigger animals.","price":5.95,"ages":[3,6]}'
  ),
  (
    'The Tale of Peter Rabbit',
    'Beatrix Potter',
    '{"description":"Rabbit eats some vegetables.","price":4.49,"ages":[2,5]}'
  ),
  (
    'Tootle',
    'Gertrude Crampton',
    '{"description":"Little toy train has big dreams.","price":3.99,"ages":[2,5]}'
  ),
  (
    'Green Eggs and Ham',
    'Dr. Seuss',
    '{"description":"Sam has changing food preferences and eats unusually colored food.","price":7.49,"ages":[4,8]}'
  ),
  (
    'Harry Potter and the Goblet of Fire',
    'J.K. Rowling',
    '{"description":"Fourth year of school starts, big drama ensues.","price":24.95,"ages":[10,99]}'
  );


select
  title,
  metadata ->> 'description' as description, -- returned as text
  metadata -> 'price' as price,
  metadata -> 'ages' -> 0 as low_age,
  metadata -> 'ages' -> 1 as high_age
from books;

validating json data

create table customers (
  id serial primary key,
  metadata json
);

alter table customers
add constraint check_metadata check (
  json_matches_schema(
    '{
        "type": "object",
        "properties": {
            "tags": {
                "type": "array",
                "items": {
                    "type": "string",
                    "maxLength": 16
                }
            }
        }
    }',
    metadata
  )
);

select
  extensions.json_matches_schema(
    '{
      "type": "object",
      "properties": {
        "name": {
          "type":"string"
        },
        "height": {
          "type": "integer"
        },
        "address": {
          "type": "object",
          "properties": {
            "city": {
              "type": "string"
            },
            "country": {
              "type": "string"
            }
          }
        },
        "color": {
          "enum": ["red", "blue", "green"]
        },
        "center": {
          "type": "object",
          "properties": {
            "x": {
              "type": "number"
            },
            "y": {
              "type": "number"
            }
          }
        }
      },
      "required": ["name", "height"],
      "additionalProperties": false
    }',
    '{
      "name": "john",
      "height": 163,
      "address": {
        "city": "phnom penh",
        "country": "Cambodia"
      },
      "color": "blue",
      "center": {
        "x": 123.2,
        "y": 452.22
      }
    }'
  )