Query for retrieve matching json Objects as a list

Question

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

This link provide json operator you can use: https://www.postgresql.org/docs/9.3/functions-json.html — Meyssam Toluie, Mar 08 '22 at 23:10
@MeyssamToluie the link you provided is to an EOL version of Postgres. Furthermore there have been a lot of additions to the `JSON` functions and a whole new JSON type `JSONB` added along with functions for the new type. The appropriate link is [JSON(B) Functions](https://www.postgresql.org/docs/current/functions-json.html). — Adrian Klaver, Mar 08 '22 at 23:26
i am using JSON type, but i can't find the function which i can use to filter out the property inside the objects of the array, that's why i wrote the question here. i hope any one can suggest any solution. — mibrahim.iti, Mar 08 '22 at 23:44

score 2 · Accepted Answer · answered Mar 09 '22 at 00:21

2

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.

answered Mar 09 '22 at 00:21

Adrian Klaver

15,886
2
17
28

Just tested, works fine, thank you. – luisvenezian Mar 09 '22 at 00:40
FYI, if you use the `jsonb` type you get to use the [SQL/JSON Path Language](https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH). – Adrian Klaver Mar 09 '22 at 00:49

score 0 · Answer 2 · answered Mar 09 '22 at 09:43

with a as(
 SELECT  json_array_elements(myjson)->'financeNumber'  as col   FROM mytable)
select exists(select from a where col::text = '1236547'::text );

https://www.postgresql.org/docs/current/functions-json.html
json_array_elements return setof json, so you need cast.
Check if a row exists: Fastest check if row exists in PostgreSQL

Query for retrieve matching json Objects as a list

2 Answers2