Find rows where value in nested JSON document is greater than INT

Question

I'm a total PostgreSQL newbie. But I think it sounds interesting to use json/jsonb in a relational database.

//just for info: I try to realize it by C# .NET Core WebAPI with Dapper

I'd like to have a table with a (multidimensional) json column like, for example:

 id                                   | data                                                             
 _____________________________________|_________________________________________________________________________________
                                      |                                                                                 
 4BF30FE6-D7DD-480B-8592-DC9676576FEF | { timestamps:{ "created":1578614541, "modified":1578615707 }, "type":"single"}  
 1AC2CD8F-09D0-456C-9FD4-B63E354BD324 | { timestamps:{ "created":1578614676, "modified":1578615921 }, "type":"multiple"}
 50AD2D82-5919-4555-BCC2-B24E0DE24263 | { timestamps:{ "created":1578614552, "modified":1578615784 }, "type":"single"}  
 8C3BE671-17D1-49FD-A891-D5E69FDF7FC2 | { timestamps:{ "created":1578614685, "modified":1578615173 }, "type":"single"}   

And I want to get all ids which have data::timestamps.created greater than 1578614670.

Pseudo code:

SELECT id, data FROM table WHERE data::timestamps.created > 1578614670;

 id                                   | data                                                             
 _____________________________________|_________________________________________________________________________________
                                      |                                                                                 
 1AC2CD8F-09D0-456C-9FD4-B63E354BD324 | { timestamps:{ "created":1578614676, "modified":1578615921 }, "type":"multiple"}
 8C3BE671-17D1-49FD-A891-D5E69FDF7FC2 | { timestamps:{ "created":1578614685, "modified":1578615173 }, "type":"single"}   

Is there a simple way to achieve this?

Answer 1

To descend multiple levels of nesting the #>> operator is useful. Then cast the extracted text to an appropriate numeric type before comparing.

SELECT id, data FROM tbl
WHERE (data #>> '{timestamps,created}')::numeric > 1578614670;

Equivalent to:

...
WHERE (data -> 'timestamps' ->> 'created')::numeric > 1578614670;

numeric is the safe bet. If you know that all numbers in created are smaller than 2^31 or 2^63 you can use integer or bigint respectively. Your sample shows valid numeric literals, but this has to hold for all extracted values.

To make it fast, consider an expression index like Miles suggested, with a cast to the appropriate numeric type - but with proper parentheses.

CREATE INDEX tbl_data_created_idx
ON tbl (((data #>> '{timestamps,created}')::numeric)); -- all parentheses required

Related:

• PostgreSQL Index on JSON

Everything here works for either type json or jsonb, all the same in this respect.

db<>fiddle here (demonstrating both)

Answer 2

Edit: Removed the errors that Erwin pointed out (and upvoted Erwin's answer). Leaving the rest of the answer since the other information might be useful.

The following will do what you ask. Here is the table.

CREATE TABLE example (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb NOT NULL
);

And once you've inserted the data, this will perform the query you want.

SELECT id, data
FROM "table"
WHERE (data->'timestamps'->>'created')::int8 > 1578614670;

This extracts the "created" entry from the JSONB column as text, and then converts to an 8-byte integer (64-bit int). For a sequential scan on a large table, this can be quite slow, so you'll want an expression index for it.

CREATE INDEX created_idx ON "table" ((data->'timestamps'->>'created')::int8);