0

I have a column as jsonb in PostgreSQL which can have the below json array references:

[
  {
    "endTime": {
      "dayOfWeek": "TUESDAY",
      "timeOfDay": {
        "hours": 14,
        "minutes": 30
      }
    },
    "skillCode": "1000124",
    "startTime": {
      "dayOfWeek": "TUESDAY",
      "timeOfDay": {
        "hours": 9,
        "minutes": 0
      }
    },
    "premiumBreak": {
      "hours": 0,
      "minutes": 0
    },
    "standardBreak": {
      "hours": 0,
      "minutes": 30
    }
  },
  {
    "endTime": {
      "dayOfWeek": "WEDNESDAY",
      "timeOfDay": {
        "hours": 14,
        "minutes": 30
      }
    },
    "skillCode": "1000176",
    "startTime": {
      "dayOfWeek": "WEDNESDAY",
      "timeOfDay": {
        "hours": 9,
        "minutes": 0
      }
    },
    "premiumBreak": {
      "hours": 0,
      "minutes": 0
    },
    "standardBreak": {
      "hours": 0,
      "minutes": 30
    }
  },
  {
    "endTime": {
      "dayOfWeek": "THURSDAY",
      "timeOfDay": {
        "hours": 14,
        "minutes": 30
      }
    },
    "skillCode": "1000176",
    "startTime": {
      "dayOfWeek": "THURSDAY",
      "timeOfDay": {
        "hours": 9,
        "minutes": 0
      }
    },
    "premiumBreak": {
      "hours": 0,
      "minutes": 0
    },
    "standardBreak": {
      "hours": 0,
      "minutes": 30
    }
  }
]

I have 10k+ records and have to fetch records based on the below criteria :

Records which has this array objects which has-> Same "endTime", "startTime" but different "skillCode".

How to write a query to get lists of all records having the above criteria.

What I have done :

  1. I am able to get records based on the json object array column based on one key.

SELECT * FROM table t WHERE EXISTS ( SELECT * FROM jsonb_array_elements(t.column) AS jsond WHERE (jsond ->> 'skillCode')::int = 1000128);

  1. But not able to get how to compare the objects among themselves like we put over a loop and check-in Java code.
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55

0 Answers0