将JSON包装到输出Stream Analytics查询中 [英] Wrapping JSON into output Stream Analytics query

问题描述

我正在使用Stream Analytics查询来过滤我输入的Complex Json对象.

I am using Stream Analytics query to filter my inputted Complex Json object.

输入:

    {
  "id" : "001",
  "firstArray":[
    {
      "tid" : 9,
       "secondArray":[
         {
            "key1" : "value1",
            "key2" : "value2"
         },
         {...}
       ]
     },
     {
      "tid" : 8,
       "secondArray":[
         {
            "key1" : "value1",
            "key2" : "value2"
         },
         {...}
       ]
     }
  ]
}

这是我的查询:

   WITH T1 AS 
    (
    SELECT 
        FirstArray.ArrayValue.Tid as Tid,
        FirstArray.ArrayValue.secondArray as SecondArray
    FROM
        inputfromeventhub MySource
    OUTER APPLY GetElements(MySource.firstArray) AS FirstArray
    )
SELECT
   T1.Tid as Tid,
   SecondArray.ArrayValue.key1 as key1,
   SecondArray.ArrayValue.key2 as key2
INTO exitstream
OUTER APPLY GetElements(T1.SecondArray) as SecondArray

我得到这样的东西:

[
 {
  "tid":9,
  "key1": "value1",
  "key2": "value2"
 },
 {
  "tid":8,
  "key1": "value1",
  "key2": "value2"
 }
]

我想将此JSON数组包装到具有唯一'id'的JSON对象中，以获得类似这样的内容:

I want to wrap this JSON Array into a JSON Object with a unique 'id' to get something like this:

{
 "id":"001",
 "array":[
  {
   "tid":9,
   "key1": "value1",
   "key2": "value2"
  },
  {
   "tid":8,
   "key1": "value1",
   "key2": "value2"
  }
 ]
} 

我找不到办法.我尝试创建调用用户定义函数的第三个选择:

I cant find a way to do that. I tried creating a third select that calls a user defined function:

function main(obj) {
  var out_obj = {};
  out_obj.id = "001";
  out_obj.array = obj;

  return JSON.stringify(out_obj);
}

但是这适用于数组中的每个对象..所以我得到了:

but this is applied to each object in the array.. so I get this:

[
  { "myFunction": "{\"id\":\"001\",\"array{\"tid\":9,\"key1\":\"value1\",\"key2\":\"value2\"}"
  },
  { "myFunction": "{\"id\":\"001\",\"array{\"tid\":8,\"key1\":\"value1\",\"key2\":\"value2\"}"
  }
]

是否可以通过查询将所有嵌套对象包装在该数组中?

Is there a way to wrap all the nested objects in that array with a query?

推荐答案

根据您的要求，我测试了此问题，您可以参考以下代码段:

According to your requirement, I tested this issue, you could refer to the following snippet code:

javascript UDF

function main(obj) {
  var out_obj = [];
  for(var i=0;i<obj.length;i++){
    var o=obj[i];
    for(var j=0;j<o.secondArray.length;j++){
      o1=o.secondArray[j];
      out_obj.push({tid:o.tid,key1:o1.key1,key2:o1.key2});
    }
  }
  return out_obj;
}

查询

SELECT 
    MySource.id,
    udf.aggreate(MySource.firstArray) as array
FROM
    [inputfromeventhub] MySource

输入

{
  "id" : "001",
  "firstArray":[
    {
      "tid" : 9,
       "secondArray":[
         {
            "key1" : "value01",
            "key2" : "value02"
         },
         {
            "key1" : "value03",
            "key2" : "value04"
         }
       ]
     },
     {
      "tid" : 8,
       "secondArray":[
         {
            "key1" : "value11",
            "key2" : "value12"
         },
         {
            "key1" : "value13",
            "key2" : "value14"
         }
       ]
     }
  ]
}

输出

这篇关于将JSON包装到输出Stream Analytics查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！