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.

Him
– Him

2021-11-02 15:30:58 +00:00
Commented Nov 2, 2021 at 15:30 — Him
– Him, Commented Nov 2, 2021 at 15:30

Chandan · Accepted Answer · 2021-11-04 17:54:22Z

2

+25

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

answered Nov 4, 2021 at 17:54

Chandan

11.8k1 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL SELECT based on value in JSON object

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