Need to parse a JSON string in a MySQL dataflow? See the tutorial below.
Doesn't MySQL support JSON specific transforms?
Yes, however, Domo's MySQL 5.6 environment predates JSON parsing support which was introduced in MySQL 5.7 and expanded in MySQL8.0+.
Are there better ways to handle JSON parsing in Domo?
Domo's ETL and visualziation engine require data structured in a relational format (one value per field). Users can use custom connectors, Python scripting or MagicETL to parse large string blobs, which should scale better than parsing the same data in SQL transforms.
Can I do this as a stored procedure?
Yes, see the Domo KB Article.
At scale, stored procedures can be tuned to outperform SELECT ... INTO table; however, Onyx Reporting recommends the table approach during the initial implementation because the code may be easier to parse and troubleshoot than Dynamic SQL.
The above code works spectacularly for parsing a JSON string for a key value pair holding an array. If the datatype of the value changes, you may have to adjust the delimiters you're searching for. In a functional implementation, consider leveraging a CASE or SWITCH statement to choose the correct delimiters based on a data type argument.