Как получить данные из массива в json-файле с помощью Microsoft Azure Query

Question

Как я могу вернуть значение элемента массива в одном из моих входов, в котором индекс, в котором он находится, постоянно изменяется?

Я почти уверен, что структура моего запроса правильная. У меня есть два входа, и я использую соединение, и я успешно получаю некоторые данные из обеих таблиц. Однако мне нужно получить RemoteIpAddress из таблицы B, но он находится в массиве в формате json.

Мой запрос

Вот это текст, если вы хотите легко скопировать, вставить и / или отредактировать его:

SELECT  
A.context.data.eventTime as eventTime,
A.context.device.type as deviceType,
A.context.[user].anonId as userId,
A.context.device.roleInstance as machineName,
B.context.operation.name as eventName,
B.context.custom.dimensions[0],
--B.GetRecordPropertyValue(GetArrayElement(B.context.custom.dimensions,7), B.RemoteIpAddress) as remoteIpAddress,
--GetArrayElement(B.context.custom.dimensions,3),
--B.GetRecordPropertyValue(GetArrayElement(B.context.custom.dimensions,3), B.userName) as userName,
DATEDIFF(minute,A.context.data.eventTime,B.context.data.eventTime) as durationInMinutes



INTO DevUserlgnsOutput

FROM DevUserlgnsInput A TIMESTAMP BY A.context.data.eventTime

JOIN DevUserlgnsInput2 B TIMESTAMP BY B.context.data.eventTime
ON DATEDIFF(minute,A,B) BETWEEN 0 AND 5

Закомментированные строки не работают, поэтому я закомментировал их.

Я посмотрел это и увидел рекомендации по использованию GetRecordPropertyValue и GetArrayElement, поэтому я и сделал. Я не получаю ошибок, но возвращается ноль.

Я также обнаружил, что если я делаю B.context.custom.dimensions[0], возвращается полный массив, включающий элемент, который я хочу видеть.

Чтобы еще больше усложнить ситуацию, я понял, что положение элемента, который я хочу в массиве, не всегда одинаково. В некоторых примерах это 7, в других - 3.

Заранее спасибо.

ОБНОВЛЕНИЕ после прочтения ответа:

Мой новый запрос:

SELECT 
Events.context.data.eventTime as eventTime,
Events.context.device.type as deviceType,
mDim.ArrayValue.MachineName as machineName,
mDim.ArrayValue.UserId as userID,
mDim.ArrayValue.RemoteIpAddress as remoteIpAddress,
mDim.ArrayValue.UserName as userName,
mDim.ArrayValue.EventName as eventName

INTO DevUserlgnsOutput

FROM DevUserlgnsInput2 Events

CROSS APPLY GetArrayElements(Events.context.custom.dimensions) AS mDim

Проблема: у меня теперь есть несколько строк для одного события, каждая из которых отображает 1 атрибут, который я хочу отслеживать (остальные столбцы в каждой строке, относящиеся к массиву, равны NULL). Есть мысли о том, как это исправить?

Answer 1

Мое решение:

    WITH Events AS

(

SELECT

  context.data.EventTime as eventTime,

  context.device.type as deviceType,

  GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 7), 'MachineName') AS machineName,

  GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 8), 'UserName') AS userName,

  GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 2), 'remoteIpAddress') AS remoteIpAddress,

  GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 0), 'EventName') AS eventName,

  CASE WHEN GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 12), 'UserId') is NULL THEN GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 11), 'UserId') ELSE GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 12), 'UserId') END as userId


FROM ProdUserlgnsInput

)


SELECT eventTime, deviceType, MachineName, UserId, UserName, remoteIpAddress, eventName  INTO ProdUserlgnsOutput FROM Events

Однако мне пришлось переместить свойство EventName в основной массив, потому что оператор WITH, который я пытался использовать для получения информации из 2 отдельных массивов, не позволял мне помещать результаты в один вывод. Кроме того, поскольку индекс UserId был в основном 12, а иногда 11. Итак, чтобы отобразить фактический UserId для всех записей, я использовал синтаксис "Case When".

Я много работал, чтобы решить эту проблему, поэтому, если кто-то хочет получить более подробную информацию, пожалуйста, не стесняйтесь спрашивать.

Answer 2

Для сложной неоднородной схемы может быть проще написать пользовательскую функцию Javascript, которую можно использовать в запросе SQL для доступа к полям.

Вот блог с примерами:

https://blogs.msdn.microsoft.com/streamanalytics/2017/04/04/using-azure-stream-analytics-javascript-udf-to-lookup-values-in-json-array/

Answer 3

Вы пробовали GetArrayElements (множественное число), чтобы получить их все в перекрестном применении? https://msdn.microsoft.com/en-us/azure/stream-analytics/reference/getarrayelements-azure-stream-analytics

Answer 4

Ниже Query соответствует вашей последней структуре массива, попробуйте:

SELECT   
context.data.EventTime as eventTime,
context.device.type as deviceType,
GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 4), 'MachineName') AS machineName,  
GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 5), 'UserId') AS userId,
GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 9), 'UserName') AS userName,
GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 11), 'remoteIpAddress') AS remoteIpAddress,
GetRecordPropertyValue(GetArrayElement(context.custom.dimensions, 13), 'EventName') AS eventName     
INTO output1
FROM input1

Answer 5

Вы можете использовать UDF

function arraygetvaluebyname(arg, name) {
    var z = arg;
    for(var i=0;i<z.length;i++){
        if(name === Object.keys(z[i])[0])
        {
            return z[i][name];
        }
    }
    return null;
}