Is sp_parse_json a feasible option for complex files

[PCollins]

I'm wondering if I'm just expecting too much from sp_parse_json and would welcome others feedback based on the json file available here

One task for example is to collate a list of all import_measures and then identify which Countries apply to each.

Is this feasible as a task in SQL Anywhere or should I be looking to use another language outside of SQL Anywhere?

[MarkCulp · Accepted solution]

IMHO The sp_parse_json function is great to use for simple scenarios and consistent and relatively simple structures. SQL puts strict constraints on data types and structures so it isn't suited for data that varies. For any complex JSON or complex processing I would recommend using a different language, something that handles variable-typed data structures. My personal favorite is Perl (I've been writing perl since ~1990) but I suspect most people these days would recommend Python, or in the case of JSON Javascript would be a good language to use.

Check out external procedures in the SQLA reference. You could write your complex parsing and processing of the json data in an external procedure and then pass it back to SQLA for storage or further processing.

HTH

[PCollins · Accepted solution]

Thanks Mark, I actually went to Perl for my workaround, although I'm simply using Perl to convert the JSON to XML and reading in what data I need from that.

I'm happy that I'm not overlooking something.

[VolkerBarth · Accepted solution]

Do you use any particular library for the JSON to XML conversion? (Not that I'm proficient at all with Perl...)