Postgres jsonb and query

0
votes

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?

1
sqlpostgresqljsonb
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
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
Your sample value isn't legal JSON. Is it supposed to be an array? So '[{"end": "...", "start": "..."}, {...}]'? – Erwin Brandstetter
It gets decoded as array of objects, so i think that it's correct – Razinar
You think? This isn't a matter of opinions. ERROR: invalid input syntax for type json – Erwin Brandstetter

1 Answers

2
votes

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"}');