CODEWITHSUNDEEP
postgresql-12
Gustaf
Gustaf

Reputation: 1349

PostgreSQL SELECT based on value in JSON object

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"
  }
]

Upvotes: 1

Views: 627

Answers (1)

Chandan
Chandan

Reputation: 11797

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();

Upvotes: 2

Related Questions