Home > Blockchain >  Query nested JSON with LINQ
Query nested JSON with LINQ

Time:04-05

I have the following JSON:

{
  "rooms": [
    {
      "roomId": 1,
      "lightsPreset": [
        {
          "lightsPresetId": 1,
          "loadValues": [ 1, 2, 3 ]
        }, 
        {
          "lightsPresetId": 2,
          "loadValues": [ 11, 12, 13 ]
        }]
    },
    {
      "roomId": 2,
      "lightsPreset": [
        {
          "lightsPresetId": 1,
          "loadValues": [ 21, 22, 23 ]
        }, 
        {
          "lightsPresetId": 2,
          "loadValues": [ 211, 212, 213 ]
        }]
    }
  ]
}

and I need to get loadValues out of it (say roomId = 1 and lightsPresetId = 1)

I managed to do it using JSONPath

IEnumerable<JToken> loadValues = o.SelectTokens("$.rooms[?(@.roomId == 1)].lightsPreset[?(@.lightsPresetId == 1)].loadValues[*]"); 

but my goal is to make it work in .Net Framework 3.5 where JSONPath didn't work.

Trying this with LINQ gives me everything for roomId = 1, but I can't figure out how to query nested arrays.

JObject o = JObject.Parse(rawJson);
var itemList = from values in o["rooms"].Children()
where (decimal)values["roomId"] == 1
select values;

Thank you.

CodePudding user response:

try this

var rooms = (JArray) JObject.Parse(json)["rooms"]; 

var roomId=1;
var lightsPresetId =1;

int[] values = rooms.Where(r => (int)r["roomId"] == roomId)
    .Select(r=> r["lightsPreset"]).FirstOrDefault() 
    .FirstOrDefault(x => (int)x["lightsPresetId"] == lightsPresetId)["loadValues"]
    .ToObject<int[]>().ToArray();

CodePudding user response:

On a PC now, so I can post it up as an answer rather than comment from the phone

If you visit https://quicktype.io you can use it to generate classes from your JSON:

// <auto-generated />
//
// To parse this JSON data, add NuGet 'Newtonsoft.Json' then do:
//
//    using SomeNamespace;
//
//    var someRootClassName = SomeRootClassName.FromJson(jsonString);

namespace SomeNamespace
{
    using System;
    using System.Collections.Generic;

    using System.Globalization;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Converters;

    public partial class SomeRootClassName
    {
        [JsonProperty("rooms")]
        public List<Room> Rooms { get; set; }
    }

    public partial class Room
    {
        [JsonProperty("roomId")]
        public long RoomId { get; set; }

        [JsonProperty("lightsPreset")]
        public List<LightsPreset> LightsPresets { get; set; }
    }

    public partial class LightsPreset
    {
        [JsonProperty("lightsPresetId")]
        public long LightsPresetId { get; set; }

        [JsonProperty("loadValues")]
        public List<long> LoadValues { get; set; }
    }

    public partial class SomeRootClassName
    {
        public static SomeRootClassName FromJson(string json) => JsonConvert.DeserializeObject<SomeRootClassName>(json, SomeNamespace.Converter.Settings);
    }

    public static class Serialize
    {
        public static string ToJson(this SomeRootClassName self) => JsonConvert.SerializeObject(self, SomeNamespace.Converter.Settings);
    }

    internal static class Converter
    {
        public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
        {
            MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
            DateParseHandling = DateParseHandling.None,
            Converters =
            {
                new IsoDateTimeConverter { DateTimeStyles = DateTimeStyles.AssumeUniversal }
            },
        };
    }
}

Then you can convert your JSON to objects like this:

var root = SomeRootClassName.FromJson(rawJson);

And you can query it like:

var array = root.Rooms.First(r => r.RoomId == 1).LightsPresets.First(lp => lp.LightsPresetId == 1).LoadValues;

Or if those IDs might not exist:

var array = root.Rooms.FirstOrDefault(r => r.RoomId == 1)?.LightsPresets.First(lp => lp.LightsPresetId == 1)?.LoadValues;
if(array != null) ....

When working with LINQ, it helps to make sure that anything that is a collection/array has a plural name. Then you can easily know whether you can just access a property of it (if it's singular) or have to use some method like First, Last, Single, Any, Where etc, if it has a plural name:

root.Rooms                             //plural, collection
  .First(r => r.RoomId == 1)           //.First on a plural results in a singular, a room object
  .LightsPresets                       //access a plural property of the single room above
  .First(lp => lp.LightsPresetId == 1) //first lightpresent in many LightPresets in the room
  .LoadValues                          //plural again; it's an array. The LINQ query resolves to an array output

Give your lambda arguments sensible names too - don't use x for everything; i used r for "a room in the collection of rooms", and lp for "a lightpreset in the collection of lightpresets"

  • Related