18.11.2020 | 1 Minute

Postgres JSON beginner Class

Operators see: https://www.postgresql.org/docs/9.5/functions-json.html

Create column using jsonb

CREATE TABLE article (
    id      UUID PRIMARY KEY,
    properties JSONB NOT NULL
);

Insert values

INSERT INTO article
VALUES (gen_random_uuid(), '{
  "price": 12.1,
  "name": "Kugelschreiber",
  "tags": {
    "manufacturer": "Siemens",
    "discounted": true
  }
}')

Select value from JSON

select 
  properties -> 'price' 
  properties ->> 'price' 
from article
  • -> will extract the value as JSONB
  • ->> will extract the value as text

Compare values

SELECT *
FROM article
WHERE CAST(properties ->> 'price' AS NUMERIC) > 10

Check where value is contained

SELECT *
FROM article
WHERE properties -> 'tags' ? 'discounted'

or

SELECT *
FROM article
WHERE jsonb_exists(properties -> 'tags', 'discounted')

Check where json is contained

SELECT *
FROM article
WHERE properties -> 'tags' @> '{"manufacturer": "Siemens"}'
Simon

Softwareentwickler

Zur Übersicht

Standort Hannover

newcubator GmbH
Bödekerstraße 22
30161 Hannover

Standort Dortmund

newcubator GmbH
Westenhellweg 85-89
44137 Dortmund