Kim decided to use objects instead of arrays for an excellent and important reason:
- a JSON object is an unordered collection of name/value pairs.
- a JSON array is an ordered list of values.
CSV data is an ordered list of values, going from left to right, so a JSON array is the obvious choice. The AskTOM question concerned name/value pairs that were not necessarily in the same order and not necessarily all there! A JSON object was the natural choice.
I’m really liking JSON more and more: not only is it simple, but it seems pretty easy to figure out how best to use it for different problems.
Instead of copying everything here, I’m going to be lazy for once and invite you to read my contribution here:
You will see some of the same ideas:
- Use PIPE_CLOB to cut the CLOB into VARCHAR sized bites;
- Use JSON_ARRAY on the whole bite to escape characters if needed;
- Use REPLACE to form an overall array, but this time of objects;
- Then use JSON_TABLE to generate one row per object and one column per value.
Hope this helps…