数据探索之道：查询Web API数据中的JSON字符串列

前言

数据驱动的时代，对数据进行探索和分析变得愈发关键。Web API作为广泛应用的数据源，提供了丰富的信息和资源。然而，面对包含JSON字符串列的Web API数据时，我们常常遇到一个挑战：如何高效灵活地处理和查询这些数据？这个问题在数据探索和提取过程中频繁出现。今天以葡萄城公司的嵌入式BI工具——Wyn商业智能为例，给大家介绍一下如何查询Web API数据中的JSON字符串列。

环境准备

Wyn商业智能软件设计器

JSON字符串示例：

{
    "TimeStamp": 756812384571,
    "MethodName": "GetLedger",
    "Message": 
    {
        "ErrorCode": 1,
        "Message": "获取数据成功"
    },
    "Result": 
    [{
        "id": 57,
        "name": "123",
        "type": "机组凝结水精处理台账",
        "unit": 1,
        "description": "",
        "creator_id": 10,
        "creationtime": "2023-12-22T16:54:28.880858",
        "modification_id": 11,
        "modificationtime": "2023-13-22T16:54:28.880858",
        "contentjson": "{\"time\":\"2023-12-01\",\"PHZX\":\"1\"}",
        "rpt_date": "2023-12-01"
    }],
    "Code": 0,
    "OtherMsg": null,
    "Error": null
}

操作步骤

1）创建JSON数据源

2）填写数据源

源类型选择嵌入式，数据源填写Json字符串。（可以选择环境准备中的示例Json字符串，也可以选择其他的Json字符串）

3）预查询

填写完数据源之后，下一步时对数据进行SQL预查询，保证JSON字符串中的数据可以被查询出来。

编写SQL查询语句：

/**该语句是用来查询Json字符串中Result的字段id、name和rpt_date以及contentjson的time和PHZX**/

Select  
    tmp.id, tmp.name,tmp.rpt_date, tmp1.time, tmp1.PHZX 
From
    UnwindJson(@source, '$.Result.[*]') tmp
With 
(
id integer '$.id',
name varchar '$.name',
rpt_date datetime '$.rpt_date',
contentjson varchar
)
Outer Apply UnwindJson(contentjson) tmp1
with 
(
time datetime '$.time',
PHZX integer
)

SQL语句中用到的JSON函数：

UnwindJson：一种表值类型的函数，可分析 JSON 文本，并以行和列的形式返回 JSON 输入的对象和属性。换句话说，UnwindJson 对 JSON 文档提供行集视图，并可以指定行集中的列以及用于填充列的 JSON 属性路径，它返回一个虚拟表。
With：用于指示 UnwindJson 如何解析 JSON内容并填充列数据。一个 with 子句中包含几个（至少一个）列定义，每个都有一个列名，一个数据类型和一个可选的 JsonPath 描述符。
Outer Apply：与Join 类似，也是对两个表进行操作，子查询语句可以做为内部表。

更多JSON函数解释可以点击这里查看。

编写完SQL语句之后将其粘贴到预查询的文本框中：

图形用户界面, 应用程序

4）检查结果是否正确

现在已经将Json字符串和对应的SQL查询语句粘贴进来，下一步就是需要检查SQL语句是否可以查询出来Json字符串中的数据，点击【下一步】，并跳转到数据显示界面，如下图所示。SQL中查询的5个字段的值已经显示出来，并且和Json字符串中的值是一样的。至此就完成了Web API数据中的JSON字符串列查询的全步骤。