使用SQL从序列化数组中提取标签 [英] Extract labels from serialized array using SQL

问题描述

我无法控制此数据的存储方式(我知道标准化数据对于sql更好)，因为它是通过WordPress GravityForms插件保存的.该插件使用序列化数组来定义问题ID(field_id)，问题标签(label).我的目标是以以下格式提取这三个值:

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).

这是序列化的数组.

任何人都可以提供一个具体示例，说明如何使用sql解析这些值吗?

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

推荐答案

一个具体示例，没有.这种东西很复杂.如果您使用的是纯json格式的数据，则可以使用以下几种选择，但都不是简单的选择.

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.

您可以构建自己的解析器. uck.

You can build your own parser. Yuck.

您可以将所需的所有内容升级到刚发布的SQL 2016，并希望内置的json工具能够满足您的需要(我听过关于它们的疑惑，但不知道它们的最终形式是什么样的.太确定了，现在就更新所有数据库服务器.)

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在SimpleTalk上构建了一个json T-SQL解析器(

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.

该文章的注释中包含指向John Galt构建的CLR工具的链接(位于 https://github.com/jgcoding/J-SQL ).尽管没有做过任何复杂的事情，但我已经成功使用了它. (如果您使用的是json相对简单，则可以解决问题.)

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.)

还有其他用于SQL的json解析器，其中一些是免费的，有的是出售的.关键是不要尝试自己编写代码，而要找到并使用其他人的解决方案来满足您的要求.

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.

这篇关于使用SQL从序列化数组中提取标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！