CODEWITHSUNDEEP
mysqlmysql-json
Bhupal
Bhupal

Reputation: 151

MySQL - list out all the JSON properties as separate columns

We have unstructured data that is being stored as JSON at MySQL (one of the tables along with Structured data). We would like to extract the data, but we are not sure how to extract the JSON Data as JSON Data could contain any property (no common properties).

Could you please help me to exact all properties by not specifying the property names.

Upvotes: 0

Views: 1145

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

SQL cannot dynamically append more columns to its result set after the query begins executing and it examines data in rows. The columns must be fixed in the select-list at the time the SQL query is parsed, before the query begins executing and examining data. So you must spell out the columns in the select-list of the query. This means you must know the names of all properties in advance.

You could do a query to fetch all property names:

SELECT JSON_KEYS(mydata) FROM MyTable;

This returns arrays of keys per row. There will be a lot of duplication. In your client application, you would write code to parse the result, and form a list of distinct keys.

Then you could use that list to form a second SQL query, with one column in the select-list for each key you noted in the first step.

The alternative is to forget about returning properties in separate columns. Just return the JSON documents from the database as-is. Then explode the JSON after you fetch it in the result set, and process it in application code that way.

One way or the other, you need to write application code, either before running your query or after running your query.

Welcome to "flexible" database design! :-)

Upvotes: 1

Related Questions