CODEWITHSUNDEEP
jsonpostgresql
Vincent Hubert
Vincent Hubert

Reputation: 63

PostgreSQL JSON type & queries

Working with PostgreSQL 9.4, is it possible to find numeric values inside a JSON datatype with comparison operators (eg. give me all record where age attribute in the JSON column is superior to 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 to get all records with the "value" attribute is superior to 18

In the present case, record with id 1 would be the only result.

Equality is working (but it's a string comparison):

SELECT *  from data WHERE attributes->>'value' = '10';

How to deal with numeric ?

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"

Thanks.

Upvotes: 3

Views: 1000

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61696

The :: cast operator precedes almost any other operator in evaluation priority (except .), so you want to add parentheses:

SELECT *  from data WHERE (attributes->>'value')::integer > 18;

Standard-compliant alternative:

 SELECT *  from data WHERE cast(attributes->>'value' AS integer) > 18;

Upvotes: 4

Related Questions