Extract labels from serialized array using SQL

Question

I do not have control of how this data is stored (I know as normalized data would be better for sql), because it is saved via the WordPress GravityForms plugin. The plugin uses a serialized array to define the question id (field_id), question label (label). My goal is to extract these three values in the following format:

field_id   label
1          1. I know my organization’s mission (what it is trying to accomplish).
2          2. I know my organization’s vision (where it is trying to go in the future).

Here is the serialized array.

Can anyone please provide a specific example as to how to parse these values out with sql?

Answer 1

A specific example, no. This kind of stuff is complex. If your are working with straight json-formatted data, here are several options, none of which are simple.

You can build your own parser. Yuck.

You can upgrade everything you have to just-released SQL 2016, and hope that the built-in json tools do what you need (I've heard iffy things about them, but don't know what their final form is like. Too, updating all your database servers right now, oh sure.)

Phil Factor over on SimpleTalk built a json T-SQL parser (https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/). It looks horrible and may run poorly, but it would do the needful.

Buried in the comments of that article are links to a CLR tool that John Galt built (at https://github.com/jgcoding/J-SQL). I have used this successfully, though I haven't done anything too complex. (If you're json is relatively simple, this could do the trick.)

There are other json parsers for SQL out there, some free, some for sale. The key thing would be to not try and write your own, but rather find and use someone else's solution that addresses your requirements.