解析对象作为字符串输出的流天青分析

Question

这个问题是关于流分析。我想将一个blob导出到SQL中。我知道这个过程，我的问题是我必须使用的查询。

{"performanceCounter":[{"available_bytes":{"value":994164736.0},"categoryName":"Memory","instanceName":""}],"internal":{"data":{"id":"459bf840-d259-11e5-a640-1df0b6342362","documentVersion":"1.61"}},"context":{"device":{"type":"PC","network":"Ethernet","screenResolution":{},"locale":"en-US","id":"RD0003FF73B748","roleName":"Sdm.MyGovId.Static.Web","roleInstance":"Sdm.MyGovId.Static.Web_IN_1","oemName":"Microsoft Corporation","deviceName":"Virtual Machine","deviceModel":"Virtual Machine"},"application":{"version":"R2.0_20160205.5"},"location":{"continent":"North America","country":"United States","clientip":"104.41.209.0","province":"Washington","city":"Redmond"},"data":{"isSynthetic":false,"samplingRate":100.0,"eventTime":"2016-02-13T13:53:44.2667669Z"},"user":{"isAuthenticated":false,"anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{},"cloud":{},"serverDevice":{},"custom":{"dimensions":[],"metrics":[]},"session":{}}} 
{"performanceCounter":[{"percentage_processor_total":{"value":0.},"categoryName":"Processor","instanceName":"_Total"}],"internal":{"data":{"id":"459bf841-d259-11e5-a640-1df0b6342362","documentVersion":"1.61"}},"context":{"device":{"type":"PC","network":"Ethernet","screenResolution":{},"locale":"en-US","id":"RD0003FF73B748","roleName":"Sdm.MyGovId.Static.Web","roleInstance":"Sdm.MyGovId.Static.Web_IN_1","oemName":"Microsoft Corporation","deviceName":"Virtual Machine","deviceModel":"Virtual Machine"},"application":{"version":"R2.0_20160205.5"},"location":{"continent":"North America","country":"United States","clientip":"104.41.209.0","province":"Washington","city":"Redmond"},"data":{"isSynthetic":false,"samplingRate":100.0,"eventTime":"2016-02-13T13:53:44.2668221Z"},"user":{"isAuthenticated":false,"anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{},"cloud":{},"serverDevice":{},"custom":{"dimensions":[],"metrics":[]},"session":{}}} 
{"performanceCounter":[{"percentage_processor_time":{"value":0.0},"categoryName":"Process","instanceName":"w3wp"}],"internal":{"data":{"id":"459bf842-d259-11e5-a640-1df0b6342362","documentVersion":"1.61"}},"context":{"device":{"type":"PC","network":"Ethernet","screenResolution":{},"locale":"en-US","id":"RD0003FF73B748","roleName":"Sdm.MyGovId.Static.Web","roleInstance":"Sdm.MyGovId.Static.Web_IN_1","oemName":"Microsoft Corporation","deviceName":"Virtual Machine","deviceModel":"Virtual Machine"},"application":{"version":"R2.0_20160205.5"},"location":{"continent":"North America","country":"United States","clientip":"104.41.209.0","province":"Washington","city":"Redmond"},"data":{"isSynthetic":false,"samplingRate":100.0,"eventTime":"2016-02-13T13:53:44.2668342Z"},"user":{"isAuthenticated":false,"anonAcquisitionDate":"0001-01-01T00:00:00Z","authAcquisitionDate":"0001-01-01T00:00:00Z","accountAcquisitionDate":"0001-01-01T00:00:00Z"},"operation":{},"cloud":{},"serverDevice":{},"custom":{"dimensions":[],"metrics":[]},"session":{}}} 

那么你可以看到3个json对象哪些对象在数组performanceCounter中有不同的字段。基本上是每个对象的第一个对象。第一个是available_bytes，第二个是percentage_processor_total，第三个是percentage_processor_time。

因为我将它导出到一个名为performaceCounter的sql表中，所以我应该为每个不同的对象都有一个不同的列，所以我想将它保存到一个字符串中，然后在我的应用程序中解析它。

作为起点我有这个查询读取的输入（斑点）和写入输出（SQL）

Select GetArrayElement(A.performanceCounter,0) as a 
INTO 
    PerformanceCounterOutput 
FROM PerformanceCounterInput A 

这GetArrayElement取入的PerformanceCounter阵列的索引0，但然后写入一个不同列中查找每个对象中的每个不同字段。所以我应该有所有不同的柜台，并为每一个列，但我的想法更像是一列呼叫performanceCounterData并保存字符串如

“‘available_bytes’：‘值’：994164736.0}，‘类别名称’：”存储器”， “实例名”： “””

或该

“{” percentage_processor_total “：{” 值 “：0.}，” 类别名称 “：” 处理器”， “实例名”： “_总”} “

或

”{“percentage_processor_ti我 “：” 值 “：0.0}，” 类别名称 “：” 过程 “ ”实例名“： ”W3WP“}”

我怎么能投像一个String数组？ 我试图CAST（GetArrayElement（A.performanceCounter，0）作为为nvarchar（MAX）），但我不能。

请一些很好的帮助会得到回报

Answer 1

用下面的解决方案，我得到2列与属性的名称，另一个属性的值，这是我最初的目的

With pc as 
    (
     Select 
     GetArrayElement(A.[performanceCounter],0) as counter 
     ,A.context.data.eventTime as eventTime 
      ,A.context.location.clientip as clientIp 
      ,A.context.location.continent as continent 
      ,A.context.location.country as country 
      ,A.context.location.province as province 
      ,A.context.location.city as city 
     FROM PerformanceCounterInput A 
    ) 
     select 
    props.propertyName, 
    props.propertyValue, 
    pc.counter.categoryName, 
    pc.counter.instanceName, 
    pc.eventTime, 
    pc.clientIp, 
    pc.continent, 
    pc.country, 
    pc.province, 
    pc.city 
    from pc 
    cross apply GetRecordProperties(pc.counter) as props 
    where props.propertyname<>'categoryname' and props.propertyname<>'instancename' 

无论如何，如果有人发现了如何编写在分析纯文本的对象，仍然奖励和赞赏将

Answer 2

你可以做这样的事情的下方，这给柜台为（propertyName的，的PropertyValue）对。

with T1 as 
(
select 
    GetArrayElement(iotInput.performanceCounter, 0) Counter, 
    System.Timestamp [EventTime] 
from 
    iotInput timestamp by context.data.eventTime 
) 

select 
    [EventTime], 
    Counter.categoryName, 
    Counter.available_bytes [Value]  
from 
    T1 
where 
    Counter.categoryName = 'Memory' 

    union all 

select 
    [EventTime], 
    Counter.categoryName, 
    Counter.percentage_processor_time [Value]  
from 
    T1 
where 
    Counter.categoryName = 'Process' 

查询，让每个计数器类型一个栏也可以做，你将不得不或者通过与每一个反“案”的语句做一个连接或一组。