Query for retrieve matching json Objects as a list-CodePudding

Assume i have a table called MyTable and this table have a JSON type column called myjson and this column have next value as a json array hold multiple objects, for example like next:

[
  {
    "budgetType": "CF",
    "financeNumber": 1236547,
    "budget": 1000000
  },
  {
    "budgetType": "ENVELOPE",
    "financeNumber": 1236888,
    "budget": 2000000
  }
]

So how i can search if the record has any JSON objects inside its JSON array with financeNumber=1236547

CodePudding user response：

Something like this:

SELECT
    t.*
FROM
    "MyTable",
    LATERAL json_to_recordset(myjson) AS t ("budgetType" varchar,
        "financeNumber" int,
        budget varchar)
WHERE
    "financeNumber" = 1236547;

Obviously not tested on your data, but it should provide a starting point.