locked
Select value from a Json string with nested array RRS feed

  • Question

  • Hi, we have a Json string that look like this:    

    { 
        "CarList": [ { "Regnr": "VBX211", "Speed": 100 }, { "Regnr": "NEB436", "Speed": 100 }, { "Regnr": "FMG198", "Speed": 100 }, { "Regnr": "JBB534", "Speed": 100 } ], 
        "Time": "2015-05-11 16:37:20", 
        "RoadId": "RV1" 
    } 

    Is it possible to select "Regnr" from the array "CarList"? 

    This is the result from a SELECT * query 

    carlist[0].regnr,carlist[0].speed,carlist[1].regnr,carlist[1].speed,carlist[2].regnr,carlist[2].speed,carlist[3].regnr,carlist[3].speed,time,roadid  

    VBX211,100,NEB436,100,FMG198,100,JBB534,100,2015-05-11 16:37:20,RV1
    Tuesday, May 12, 2015 8:19 AM

Answers

  • You cannot directly choose from the array. At first you have to flatten it:

    Select A.RoadId, flat.ArrayValue.value.Regnr AS [Regnr],
     flat.ArrayValue.value.Speed AS [Speed]
     FROM [stream] A
     CROSS APPLY GetElements(A.[Carlist]) AS flat

    this will give you row for each entry. Then you go from there.


    Tuesday, May 12, 2015 3:05 PM

All replies

  • You cannot directly choose from the array. At first you have to flatten it:

    Select A.RoadId, flat.ArrayValue.value.Regnr AS [Regnr],
     flat.ArrayValue.value.Speed AS [Speed]
     FROM [stream] A
     CROSS APPLY GetElements(A.[Carlist]) AS flat

    this will give you row for each entry. Then you go from there.


    Tuesday, May 12, 2015 3:05 PM
  • Thank you, we solved it by using your answer with some modification, when we removed "value" it worked:

    Select A.RoadId, flat.ArrayValue.Regnr,
    flat.ArrayValue.Speed
    FROM [stream] A
    CROSS APPLY GetElements(A.Carlist) AS flat

    Saturday, May 16, 2015 6:34 AM