It's true that it's not a good idea to denormalize into JSON, but sometimes you need to deal with JSON data, and there's a way to extract a JSON array into rows in a query. If MySQL had a proper STRING_SPLIT(val, 'separator') table returning function, I could hack it (escaping be damned), but that's not available either. I guess what I'm looking for here is some sort of JSON_SPLIT along the lines of: SET j = ' ' įROM JSON_SPLIT(JSON_EXTRACT( '$'), '$') and have it concatenate together the values in the array vs. How can I do something like: SET j = ' ' SELECT GROUP_CONCAT(JSON_EXTRACT( CONCAT( '$'))) AS val FROM In other words, I know I can do this: SET j = ' ' Or, say I have a JSON array and want to GROUP_CONCAT() it to a single comma separated string? But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven't been able to figure that one out.įor example, say I have a JSON array and want to insert a row for each element in the array with its value? The only way I have found is to write a bunch of JSON_EXTRACT(. Grabbing JSON, manipulating and extracting arrays from it etc. I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure. UPDATE: This is now possible in MySQL 8 via the JSON_TABLE function:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |