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? — Mark Schultheiss
– Mark Schultheiss, Commented Aug 29, 2022 at 17:24

David Brabant · Accepted Answer · 2016-08-30 02:45:46Z

147

$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, 2016 at 2:45

answered May 16, 2013 at 6:57

David Brabant

44k16 gold badges93 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BaltoStar Over a year ago

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.

Tom Padilla Over a year ago

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.

Fredrik Jönsson Over a year ago

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.

Dixit Singla Over a year ago

how about if I have to put multiple conditions(OR, AND) in where? Like Name eq "Darts" AND Type eq "Fun Stuff"

Community · Accepted Answer · 2017-05-23 12:02:45Z

52

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, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 16, 2014 at 12:28

Xero

5611 gold badge5 silver badges6 bronze badges

3 Comments

NoBrassRing Over a year ago

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 Over a year ago

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)

Mark Schultheiss Over a year ago

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...

Mogsdad · Accepted Answer · 2016-03-12 00:15:39Z

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, 2016 at 0:15

Mogsdad

46k21 gold badges166 silver badges289 bronze badges

answered Mar 12, 2016 at 0:10

Grant Letourneau

2392 silver badges2 bronze badges

Comments

andrew pate · Accepted Answer · 2019-09-25 16:02:29Z

11

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!

answered Sep 25, 2019 at 16:02

andrew pate

4,3771 gold badge43 silver badges32 bronze badges

Comments

Maybe · Accepted Answer · 2022-08-30 19:01:34Z

5

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.

edited Aug 30, 2022 at 19:01

answered May 14, 2020 at 20:47

Maybe

1,13613 silver badges15 bronze badges

1 Comment

Mark Schultheiss Over a year ago

"(this is so much easier in PowerShell 7)" consider adding that as an additional example i.e. "how, code example noted as PS7"

Nikheel · Accepted Answer · 2019-01-07 07:16:28Z

2

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
}

answered Jan 7, 2019 at 7:16

Nikheel

2354 silver badges7 bronze badges

Collectives™ on Stack Overflow

PowerShell : retrieve JSON object by field value

6 Answers 6

4 Comments

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related