How to get data from an array in a json file through a Microsoft Azure Query

Question

How do I return the value of an element of an array in one of my inputs in which the index it's located at constantly changes?

I'm pretty sure the structure of my query is correct. I have two inputs and am using a join and am successfully getting some data from both tables successfully. However, I need to get the RemoteIpAddress from table B, but it is in an array in json format.

My Query

Here it is in text if you want to easily copy, paste, and/or edit it:

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

The commented out lines do not work, so I've commented them out.

I've looked this up and saw recommendations to use GetRecordPropertyValue and GetArrayElement, so I did. I'm getting no errors, but it's returning null.

I also discovered that if I do B.context.custom.dimensions[0], the full array including the element I want to see is returned.

To further complicate things, I realized that the position of the element I want in the array is not always the same. In some sample data, it's 7, others it's 3.

Thanks in advance.

UPDATE after reading an answer:

My new query:

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

Problem: I now have multiple rows for a single event, each one displaying 1 attribute I want to track (the rest of the columns in each row pertaining to the array are NULL). Any thoughts on how to fix that?

Answer 1

My solution:

    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

However, I had to move the EventName property to the main array because the WITH statement I tried to use to get info from 2 separate arrays was not letting me put the results in a single output. Additionally, since the index of UserId was mostly 12, but sometimes 11. So, in order to display the actual UserId for all records, I used the "Case When" syntax.

I worked a lot to solve this problem, so if anyone wants more details, please feel free to ask.