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: postgresql.org/docs/9.3/functions-json.html — Meyssam Toluie
– Meyssam Toluie, Commented Mar 8, 2022 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. — Adrian Klaver
– Adrian Klaver, Commented Mar 8, 2022 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
– mibrahim.iti, Commented Mar 8, 2022 at 23:44

Adrian Klaver · Accepted Answer · 2022-03-09 00:21:57Z

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 9, 2022 at 0:21

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

1 Comment

Adrian Klaver Over a year ago

FYI, if you use the jsonb type you get to use the SQL/JSON Path Language.

jian · Accepted Answer · 2022-03-09 09:43:54Z

0

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

answered Mar 9, 2022 at 9:43

jian

5,0231 gold badge29 silver badges46 bronze badges

Collectives™ on Stack Overflow

Query for retrieve matching json Objects as a list

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related