PostgreSQL JSON Type and Queries
Working with PostgreSQL 9.4 allows you to find numeric values inside a JSON datatype with comparison operators (for example, give me an entire record where the age attribute in a JSON column is greater than 18)?
CREATE TABLE data
(
id serial NOT NULL,
attributes jsonb
);
INSERT INTO data (id, attributes) VALUES (1, '{"name": "Value A", "value": 20}');
INSERT INTO data (id, attributes) VALUES (2, '{"name": "Value B", "value": 10}');
I would like to know how to query this table so that all records with the "value" attribute exceed 18
In this case, the record with ID 1 will be the only result.
Equality works (but this is a string comparison):
SELECT * from data WHERE attributes->>'value' = '10';
How to work with numbers?
SELECT * from data WHERE attributes->>'value' > 18;
==> ERROR: operator does not exist: text > integer
SELECT * from data WHERE attributes->>'value'::integer > 18;
==> ERROR: invalid input syntax for integer: "value"
Thank.
+3
source to share
1 answer
The casting operator ::
precedes almost any other operator in evaluation precedence (except .
), so you want to add parentheses:
SELECT * from data WHERE (attributes->>'value')::integer > 18;
Standard Compatible Alternative:
SELECT * from data WHERE cast(attributes->>'value' AS integer) > 18;
+4
source to share