Evaluate column value into rows

Question

I have a column whose value is a json array. For example:

[{"att1": "1", "att2": "2"}, {"att1": "3", "att2": "4"}, {"att1": "5", "att2": "6"}]

What i would like is to provide a view where each element of the json array is transformed into a row and the attributes of each json object into columns. Keep in mind that the json array doesn't have a fixed size.

Any ideas on how i can achieve this ?

Answer 1

a stored procedure lexer to run against the string? anything else like trying a variable in the SQL or using regexp i imagine will be tricky.

if you need it for client-side viewing only, can you use JSON decode libraries (json_decode() if you are on PHP) and then build markup from that?

but if you're gonna use it for any Db work at all, i reckon it shouldn't be stored as JSON.