C
C#•3y ago
Foxtrek_64

JsonPath Query [Answered]

I have some json that looks like this: 
{
    "teams": [
        {
            "BusinessUnitId": 1234,
            "TeamId": 5678,
            "TeamName": "Foo",
            "IsActive": true,
            "Description": "",
            "Notes": ""
        },
        {
            ...
        }
    ]
}
{
    "teams": [
        {
            "BusinessUnitId": 1234,
            "TeamId": 5678,
            "TeamName": "Foo",
            "IsActive": true,
            "Description": "",
            "Notes": ""
        },
        {
            ...
        }
    ]
}
Using a JsonPath query, I want to filter results where IsActive is true and return the TeamName. I have put together this query: 
var document = JsonDocument.Parse(rawJson);
var teams = document.SelectElements("$.teams[?(@.IsActive == true)].TeamName").ToList();
var document = JsonDocument.Parse(rawJson);
var teams = document.SelectElements("$.teams[?(@.IsActive == true)].TeamName").ToList();
The way I read it, it should select from teams where IsActive is true, then return the TeamName property, however the resulting teams object is always null (specifically, for whatever reason it returns List<JsonElement?>?). Removing the .TeamName reference at the end also returns a null collection. According to this stackoverflow post, the JsonPath query is properly formatted (https://stackoverflow.com/questions/46931964/json-path-to-check-equals-condition). Any thoughts of what I might be doing wrong here? I'm using System.Text.Json and the JsonDocumentPath libraries.
Stack Overflow
JSON Path to check equals condition
I am a newbie to Json Path. I am using Apache camel along with the json path. I am trying to check the json path success to evaluate the value to true. Json Message: { "success": true, "
7 Replies
shua
shua•3y ago
Don't do it this way. Map the JSON to a class and deserialize it using Newtonsoft i.e. 
var teamsList = JsonConvert.Deserialize<List<Team>>(json);

var activeTeamsList = teamsList.Where(t => t.IsActive);
var teamsList = JsonConvert.Deserialize<List<Team>>(json);

var activeTeamsList = teamsList.Where(t => t.IsActive);
something like that.
Wz
Wz•3y ago
maybe you can try https://json-everything.net/json-path
json-everything.net
Extended JSON support in .Net built on top of the System.Text.Json namespace
Foxtrek_64
Foxtrek_64OP•3y ago
I'm needing to do the json path because if I use code like yours, I get a json parse exception when deserializing because of how S.T.J handles the teams node. That said, even moving the IsActive and TeamName to C#, I get this: 
var rawJson = File.ReadAllText(@"C:\temp\2022-08-26-Teams.json");
var document = JsonDocument.Parse(rawJson);
var teams = document.SelectElements("$.teams").Where(team => team is { }).ToList();
var teamsList = teams.Select(team => team!.Value.Deserialize<Team>()); // The JSON value could not be converted to TeamParser.Team. Path: $ | LineNumber: 0 | BytePositionInLine: 1.
var activeTeams = teamsList.Where(team => team is { IsActive: true });
string teamNames = string.Join(",", activeTeams.Select(team => team!.TeamName));
var rawJson = File.ReadAllText(@"C:\temp\2022-08-26-Teams.json");
var document = JsonDocument.Parse(rawJson);
var teams = document.SelectElements("$.teams").Where(team => team is { }).ToList();
var teamsList = teams.Select(team => team!.Value.Deserialize<Team>()); // The JSON value could not be converted to TeamParser.Team. Path: $ | LineNumber: 0 | BytePositionInLine: 1.
var activeTeams = teamsList.Where(team => team is { IsActive: true });
string teamNames = string.Join(",", activeTeams.Select(team => team!.TeamName));
I'll give this library a try. I had been using a different json path library
shua
shua•3y ago
Wtf is STj Your getting a exception because your doing something wrong
Foxtrek_64
Foxtrek_64OP•3y ago
System.Text.Json, the .net json parsing library. The code you provided was for Newtonsoft.Json Json-Everything worked. 
var rawJson = File.ReadAllText(@"C:\temp\2022-08-26-Teams.json");
var jsonPath = JsonPath.Parse("$.teams[?(@.IsActive == true)].TeamName");

var instance = JsonDocument.Parse(rawJson);
var pathResult = jsonPath.Evaluate(instance.RootElement);
if (pathResult.Error is string error)
{
    Console.WriteLine(error);
    return;
}

// Not null when error is null
var teamList = pathResult.Matches!.Select(match => match.Value.GetString());
string teamNames = string.Join(",", teamList);
Console.WriteLine(teamNames);
var rawJson = File.ReadAllText(@"C:\temp\2022-08-26-Teams.json");
var jsonPath = JsonPath.Parse("$.teams[?(@.IsActive == true)].TeamName");

var instance = JsonDocument.Parse(rawJson);
var pathResult = jsonPath.Evaluate(instance.RootElement);
if (pathResult.Error is string error)
{
    Console.WriteLine(error);
    return;
}

// Not null when error is null
var teamList = pathResult.Matches!.Select(match => match.Value.GetString());
string teamNames = string.Join(",", teamList);
Console.WriteLine(teamNames);
shua
shua•3y ago
I can show you later today when I have time but your overcomplicating that code majorly
Accord
Accord•3y ago
✅ This post has been marked as answered!

Did you find this page helpful?