PostgreSQL JSON type & queries

Question

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.

Answer 1

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;