I have this data in a json column #mycolumn { "key": "KEY", "elements": [ { "name": "NAME_1" }, { "name": "NAME_2" } { "name": "NAME_3" } ] } I want to select all the elements[*].name. I have tried: select JSON_EXTRACT_PATH_TEXT(mycolumn,…
I have a students_data table with a json column like this. CREATE TABLE students_data (doc_id INT, doc_data JSON); A row is inserted with doc_id = 101. The json content in the row is: { "document_type": "students_report", "document_name": "students_report_202406.pdf", "data": {…
I have a table with a JSON column. The JSON column holds for each row, a JSON array with JSON objects such as this [{"my_value": 42, "category": "A"}, {"my_value": 100, "category": "C"}, {"my_value": 15, "category": "B"}, {"my_value": 123, "category": "D"}]…
I have a table 'sometable' with a JSON field named 'jsonval', which contains a JSON object with the property 'programs' that contains an array of objects with the property 'id' I want to search for records that contain jsonval.programs.id =…
{ "a": [ { "b": { "c": [ { "id": "123" } ], "d": { "e": { "f": [ { "name": "abc", "type": "in" }, { "name": "xyz", "type": "out" } ] } } } }, { "b": { "c":…
I have a JSON column that stores an array of objects, each object having 2 properties: [ { "title" : "CALCULATED", "applies" : true }, { "title" : "APPROVED", "applies" : false }, { "title" : "RECIEVED", "applies" : false…
id lessons 1 ["3M-232","3M-313"] 2 ["3M-311","3M-312"] 3 ["3M-443","3M-565"] 4 ["4M-232","4M-313"] 5 ["4M-311","4M-312"] 6 ["4M-443","4M-565"] How can get rows that match lessons with "3M-232" as one of them SELECT * FROM merged_lesson where lessons = "3M-232"; Expected only row 1 to…
I am getting below response while running an API. { "bookingId": 0, "bookingIds": { "13320134": "2023-05-01T04:30:00", "13320135": "2023-05-02T04:30:00", "13320136": "2023-05-03T04:30:00" }, "conflictDates": null, "recurrenceID": 1710459 } I want to get the first value inside 'bookingIds' 13320134 What I tried: $.bookingIds.13320134…
My table looks like this: id data 1 {tags: {"env": "dev", "owner": "me"}} I want to fetch the data and inside the select query convert the data column to the following format: id data 1 {tags: [{"key": "env", "value": "dev"},…