18.11.2020 |

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"}'
Zur Übersicht
Simon Jakubowski

Mehr vom Devsquad...

sepideh adelpour

CSS Animate

Umut Tufanoglu

NGINX Config Generator

Hallo, ich bin Jörg Herbst!

Ich bin der CEO von newcubator und freue mich über jede Nachricht!

* Pflichtfeld