In my Postgres 13.10 database, I have a table menus with a JSON column named dishes. This column contains values in the following format: { "desserts": [ {"id": 1, "name": "chocolate cake"}, {"id": 2, "name": "banana split"} ], "appetizers": […
I want to use JSONata (https://try.jsonata.org/) or JSONPath to combine column data from a Columns array with row data from a Rows array in the same JSON. I think I need to have a for each row with another for…
How can I use a parameter within a JSONPath expression in a PostgreSQL SQL query? I want to query something like this: SELECT * FROM table WHERE jsondata @@ '$.pathto.array[*].** ? (@.someproperty == ":VALUE").anotherproperty != null'; I tried: SELECT *…
I'm trying to get a JSON path for the HTML which has the language code DE Here is the sample JSON { "name": "Name", "text": "", "html": "HTML content", "tags": [], "translations": [ { "html": "HTML I don't want", "text":…
I have the following json and want to create a list from the display names, the json can have many different types not necessarily only 2: {"data": [ { "types": { "1": { "display_name": "123", }, "2": { "display_name": "321",…
I'm trying to run the following query, which unless I'm misunderstanding the documentation looks as like it should be possible. SELECT id, profile FROM end_user WHERE profile @? '$.first_segment_exited_at ? (@.datetime() < "2023-05-25".datetime())'::jsonpath; This is the documentation which leads me…
I can find a person who has a hobby of "Dance" by doing the following: Xpath = Persons/Person/Hobbies/Hobby[text()="Dance"]/../.. <Persons> <Person> <Name>David</Name> <Age>20</Age> <Hobbies> <Hobby>Sing</Hobby> <Hobby>Dance</Hobby> </Hobbies> </Person> <Person> <Name>Frank</Name> <Age>30</Age> <Hobbies> <Hobby>Sing</Hobby> <Hobby>Read</Hobby> </Hobbies> </Person> </Persons> How would I do…
I have a json value that can contain a single record or an array, I need to extract the ID field of these records and gather them in an array. The resulting element should be an array with 1 or…
I have a JSON file having customer data as below . Here I want to extract the custName & CustId where amount is greater than 10 . But I'm stuck as unable to get the desired jsonpath for extracting these…
I have the following JSON: { "A":{ "C":{ "date":"2022-01-23" } }, "B":{ "D":{ "date":"2022-01-24" } } } I would like to extract the value $.A.C.date if it is not null, else I would like to extract $.B.D.date. Basically I would…