PowerShell : retrieve JSON object by field value

Question

Consider JSON in this format :

"Stuffs": [
    {
        "Name": "Darts",
        "Type": "Fun Stuff"
    },
    {
        "Name": "Clean Toilet",
        "Type": "Boring Stuff"
    }
]

In PowerShell 3, we can obtain a list of Stuffs :

$JSON = Get-Content $jsonConfigFile | Out-String | ConvertFrom-Json

Assuming we don't know the exact contents of the list, including the ordering of the objects, how can we retrieve the object(s) with a specific value for the Name field ?

Brute force, we could iterate through the list :

foreach( $Stuff in $JSON.Stuffs ) {

But I am hopeful there exists a more direct mechanism ( similar to Lync or Lambda expressions in C# ).

Does this answer your question? [How do you get the name field of a JSON object in Powershell if you don't know it?](https://stackoverflow.com/questions/23062087/how-do-you-get-the-name-field-of-a-json-object-in-powershell-if-you-dont-know-i) — Mark Schultheiss, Aug 29 '22 at 17:24

David Brabant · Answer 1 · 2016-08-30T02:45:46.197

138

$json = @"
{
"Stuffs": 
    [
        {
            "Name": "Darts",
            "Type": "Fun Stuff"
        },

        {
            "Name": "Clean Toilet",
            "Type": "Boring Stuff"
        }
    ]
}
"@

$x = $json | ConvertFrom-Json

$x.Stuffs[0] # access to Darts
$x.Stuffs[1] # access to Clean Toilet
$darts = $x.Stuffs | where { $_.Name -eq "Darts" } #Darts

edited Aug 30 '16 at 02:45

answered May 16 '13 at 06:57

David Brabant

41,623
16
83
111

Thanks David. I have amended my question to be more clear. Your answer won't work because the code will not know ahead of time how the JSON is structured. It could be that Darts is in slot 0 and Clean Toilet in slot 1, but it could also be that Clean Toilet is in slot 0 and Darts is in slot 1. Therefore I need some way to match objects on the Name field. – BaltoStar May 16 '13 at 11:00
1

Not to be picky but... Isn't this just a "glorified" brute force? It's not like the Where-Object isn't looping through the collection just like foreach. In fact, I think Where-Object uses foreach anyway. – Tom Padilla Jun 25 '20 at 14:02
Unless PS for some reason would keep a hashtable index on *every* key in any JSON structure there is no way around a "brute force" here. You could arguably hide it with syntax in some other languages, but that would be all. – Fredrik Jönsson Oct 28 '21 at 06:39
how about if I have to put multiple conditions(OR, AND) in where? Like `Name eq "Darts" AND Type eq "Fun Stuff"` – Dixit Singla Jan 06 '23 at 09:14

score 48 · Answer 2 · edited May 23 '17 at 12:02

48

I just asked the same question here: https://stackoverflow.com/a/23062370/3532136 It has a good solution. I hope it helps ^^. In resume, you can use this:

The Json file in my case was called jsonfile.json:

{
    "CARD_MODEL_TITLE": "OWNER'S MANUAL",
    "CARD_MODEL_SUBTITLE": "Configure your download",
    "CARD_MODEL_SELECT": "Select Model",
    "CARD_LANG_TITLE": "Select Language",
    "CARD_LANG_DEVICE_LANG": "Your device",
    "CARD_YEAR_TITLE": "Select Model Year",
    "CARD_YEAR_LATEST": "(Latest)",
    "STEPS_MODEL": "Model",
    "STEPS_LANGUAGE": "Language",
    "STEPS_YEAR": "Model Year",
    "BUTTON_BACK": "Back",
    "BUTTON_NEXT": "Next",
    "BUTTON_CLOSE": "Close"
}

Code:

$json = (Get-Content "jsonfile.json" -Raw) | ConvertFrom-Json

$json.psobject.properties.name

Output:

CARD_MODEL_TITLE
CARD_MODEL_SUBTITLE
CARD_MODEL_SELECT
CARD_LANG_TITLE
CARD_LANG_DEVICE_LANG
CARD_YEAR_TITLE
CARD_YEAR_LATEST
STEPS_MODEL
STEPS_LANGUAGE
STEPS_YEAR
BUTTON_BACK
BUTTON_NEXT
BUTTON_CLOSE

Thanks to mjolinor.

edited May 23 '17 at 12:02

Community

1
1

answered Apr 16 '14 at 12:28

Xero

521
1
5
6

Warning: A JSON object can contain 0 or more name-value pairs. This solution works nicely unless the object has 0 name-value pairs. Then powershell fails: `PS > $el = ConvertFrom-Json "{ }"` `PS> $el.psobject.properties.name` `The property 'name' cannot be found on this object. Verify that the property exists. ` – NoBrassRing Nov 09 '19 at 14:45
Another warning: If a JSON object (say $el) has 1 name-value pair then $el.psobject.properties.name returns the length of the name in characters rather than the number of name-value pairs (i.e. 1) – NoBrassRing Nov 10 '19 at 00:46
Given your first sentence you should/have voted to close this one as a duplicate instead. OR at least one of these two is a duplicate... – Mark Schultheiss Aug 29 '22 at 17:24

score 23 · Answer 3 · edited Mar 12 '16 at 00:15

23

David Brabant's answer led me to what I needed, with this addition:

x.Stuffs | where { $_.Name -eq "Darts" } | Select -ExpandProperty Type

edited Mar 12 '16 at 00:15

Mogsdad

44,709
21
151
275

answered Mar 12 '16 at 00:10

Grant Letourneau

239
2
2

score 9 · Answer 4 · answered Sep 25 '19 at 16:02

Hows about this:

$json=Get-Content -Raw -Path 'my.json' | Out-String | ConvertFrom-Json
$foo="TheVariableYourUsingToSelectSomething"
$json.SomePathYouKnow.psobject.properties.Where({$_.name -eq $foo}).value

which would select from json structured

{"SomePathYouKnow":{"TheVariableYourUsingToSelectSomething": "Tada!"}

This is based on this accessing values in powershell SO question . Isn't powershell fabulous!

Vopel · Answer 5 · 2022-08-30T19:01:34.610

In regards to PowerShell 5.1 ...

Operating off the assumption that we have a file named jsonConfigFile.json with the following content from your post:

{
    "Stuffs": [
        {
            "Name": "Darts",
            "Type": "Fun Stuff"
        },
        {
            "Name": "Clean Toilet",
            "Type": "Boring Stuff"
        }
    ]
}

This will create an ordered hashtable from a JSON file to help make retrieval easier:

$json = [ordered]@{}

(Get-Content "jsonConfigFile.json" -Raw | ConvertFrom-Json).PSObject.Properties |
    ForEach-Object { $json[$_.Name] = $_.Value }

$json.Stuffs will list a nice hashtable, but it gets a little more complicated from here. Say you want the Type key's value associated with the Clean Toilet key, you would retrieve it like this:

$json.Stuffs.Where({$_.Name -eq "Clean Toilet"}).Type

It's a pain in the ass, but if your goal is to use JSON on a barebones Windows 10 installation, this is the best way to do it as far as I've found.

"(this is so much easier in PowerShell 7)" consider adding that as an additional example i.e. "how, code example noted as PS7" — Mark Schultheiss, Aug 29 '22 at 17:20

score 2 · Answer 6 · answered Jan 07 '19 at 07:16

This is my json data:

[
   {
      "name":"Test",
      "value":"TestValue"
   },
   {
      "name":"Test",
      "value":"TestValue"
   }
]

Powershell script:

$data = Get-Content "Path to json file" | Out-String | ConvertFrom-Json

foreach ($line in $data) {
     $line.name
}

PowerShell : retrieve JSON object by field value

6 Answers6

Linked

Related