Postgres jsonb and query

Question

I have a postgres database. Inside "match" table I store a JSONB like this one, inside "when" column:

{{"end": "2017-01-04T17:22:13.311693Z", "start": "2017-01-04T16:22:13.311693Z"}, {"end": "2017-01-04T17:22:13.311693Z", "start": "2017-01-04T16:22:13.311693Z"}}

and it works like a list of object storing a start time and an end time. Now i want to query all matches that are not past. How can i do that? Is it possible to compare a single "start" value with the current data?

Missing, helpful info: Your Postgres version; table definition (CREATE TABLE statement showing data types & constraints; what you have so far (even if it's not working); desired example result. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 3, 2018 at 14:54
Postgres version: 9.6.2, table definition is not crucial here because it's a sample for a more complex scenario. "when" column is just defined as {:array, :map} in elixir (Ecto migration), and stored as jsonb in my database. Desired result: SELECT * FROM Matches WHERE at least one "end" field inside json array is > than current utc datetime — Razinar
– Razinar, Commented Apr 3, 2018 at 15:59
Your sample value isn't legal JSON. Is it supposed to be an array? So '[{"end": "...", "start": "..."}, {...}]'? — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 3, 2018 at 16:46
It gets decoded as array of objects, so i think that it's correct — Razinar
– Razinar, Commented Apr 3, 2018 at 16:49
You think? This isn't a matter of opinions. ERROR: invalid input syntax for type json — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 3, 2018 at 16:51

rotarydial · Accepted Answer · 2018-04-05 21:45:18Z

Have a look at this SQL Fiddle.

You can query into the jsonb using the ->> operator. For dates/times, you'll need to convert the results in order to compare. This query returns only values with a future start:

SELECT when_col->>'start' FROM match
WHERE to_date(when_col->>'start','YYYY-MM-DD"T"HH24:MI:SS') >= NOW();

Note that the JSON you gave above was malformed. I broke it out into separate rows in the fiddle:

CREATE TABLE match
(
  when_col jsonb
 );

 INSERT INTO match (when_col) VALUES ('{"end": "2017-01-04T17:22:13.311693Z", "start": "2017-01-04T16:22:13.311693Z"}');
 INSERT INTO match (when_col) VALUES ('{"end": "2017-02-04T17:22:13.311693Z", "start": "2017-02-04T16:22:13.311693Z"}');
 INSERT INTO match (when_col) VALUES ('{"end": "2019-02-04T17:22:13.311693Z", "start": "2019-02-04T16:22:13.311693Z"}');
 INSERT INTO match (when_col) VALUES ('{"end": "2029-02-04T17:22:13.311693Z", "start": "2029-02-04T16:22:13.311693Z"}');

Collectives™ on Stack Overflow

Postgres jsonb and query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related