I’ve a many to many relationship with this tables Table assets: id name other fields 19 asset 1 … 20 asset 2 … … … … Branches table: id name other fields 1 branch 1 … 2 branch 2 … … … … branch_r_asset: branch_id asset_id 1 1 1 2 2 14 n n I… Read More MySQL json_object and json_arrayagg with group by not working as expected
Can someone help explain the following behavior? As near as I can tell, it looks like when a JSON object is assigned to a mysql variable, it may or may not be escaped depending upon the context. So let’s try it mysql> CREATE TABLE `json_test` (`jdata` json DEFAULT NULL); Query OK, 0 rows affected (0.09… Read More Why is mysql JSON_OBJECT variable assignment inconsistent?
I have a MySQL table named computers with a column named details that is json data type formatted. I’ve inserted a value like this in that column: ‘{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }’ I can simple get Chrome value by the following query: SELECT details ->> ‘$.name’ FROM… Read More How to select a nested item of json datatype?
Stuck in one exercise task where I want JSON objects to be in form of a JSON array. Actually I have reached up to 1st stage to convert things into JSON object first. Second stage is where my output should be as same as expected output as below, but it is not coming. Current output… Read More How to convert JSON object rows into JSON array in MySQL?
How can I access values in the status and date columns stored as JSON? Please, have a look at an example row below. {"1":{"status":true,"date":"2022-03-30"},"3":{"status":true,"date":"2022-03-30"}} >Solution : Demo: set @j = ‘{"1":{"status":true,"date":"2022-03-30"},"3":{"status":true,"date":"2022-03-30"}}’; select json_extract(@j, ‘$."1".status’) as status; +——–+ | status | +——–+ | true | +——–+ In this case, it may be unexpected that you need… Read More Access JSON column
I have a JSON type field in my table, which has a value like this [1, 3] and I want to insert another value in the middle of the array, using something like a json splice function (I know it doesn’t exist) /* JSON_ARRAY_SPLICE(array, start, deleteCount, itemToInsert) */ JSON_ARRAY_SPLICE(‘[1, 3]’, 1, 0, 2) >>> [1,… Read More How to insert new value in the middle of a JSON array?
I have a SQL table that looks something like this: | ID | Value | | — | —————————————————– | | 1 | {"name":"joe", "lastname":"doe", "age":"34"} | | 2 | {"name":"jane", "lastname":"doe", "age":"29"} | | 3 | {"name":"michael", "lastname":"dumplings", "age":"40"}| How can I using SQL select function, select only the rows where "age" (in value… Read More Looping through an array in SQL column