PostgreSQL SELECT based on value in JSON object

Question

I would like to query a table, orders, anad select all rows based on a value in a json object in a column, updates.

I want to select all rows that are within 5 days from the last time it was closed.

It is a PostgreSQL 12 database.

[
  {
    "time": {
      "__type": "Date",
      "iso": "2021-09-16T09:31:57.976Z"
    },
    "userId": "xKn5A1GuLV",
    "to": "created",
    "role": "admin"
  },
  {
    "time": {
      "__type": "Date",
      "iso": "2021-09-16T09:31:57.976Z"
    },
    "userId": "xKn5A1GuLV",
    "to": "opened",
    "role": "admin",
    "from": "created"
  },
  {
    "time": {
      "__type": "Date",
      "iso": "2021-10-12T12:10:44.688Z"
    },
    "userId": "Hd37AyKJsN",
    "to": "closed",
    "role": "admin",
    "from": "opened"
  },
  {
    "time": {
      "__type": "Date",
      "iso": "2021-10-12T12:10:54.224Z"
    },
    "userId": "Hd37AyKJsN",
    "to": "opened",
    "role": "admin",
    "from": "closed"
  },
  {
    "time": {
      "__type": "Date",
      "iso": "2021-10-12T12:40:58.476Z"
    },
    "userId": "Hd37AyKJsN",
    "to": "closed",
    "role": "admin",
    "from": "opened"
  }
]

[Here's the docs on this](https://www.postgresql.org/docs/current/functions-json.html). — Him, Nov 02 '21 at 15:30

score 2 · Accepted Answer · answered Nov 04 '21 at 17:54

If you have object in updates column for eg:

{
    "time": {
      "__type": "Date",
      "iso": "2021-10-12T12:10:54.224Z"
    },
    "userId": "Hd37AyKJsN",
    "to": "opened",
    "role": "admin",
    "from": "closed"
}

Then query would look something like:

select * from orders where (updates #>> 'time,iso')::timestamp Between NOW() - INTERVAL '5 day' AND NOW();

If you have array in updates column for eg:

[{
    "time": {
      "__type": "Date",
      "iso": "2021-10-12T12:10:54.224Z"
    },
    "userId": "Hd37AyKJsN",
    "to": "opened",
    "role": "admin",
    "from": "closed"
}]

Then query would look something like:

select * from orders where (updates #>> '0,time,iso')::timestamp Between NOW() - INTERVAL '5 day' AND NOW();

PostgreSQL SELECT based on value in JSON object

1 Answers1