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) > 10Check 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"}'