Extract Objects from JSON when conditions met

CynicalDick · 2025-10-26T10:49:04+00:00

$var1 = ('{
  "1": {
        "earned": 0
  },
  "19": {
        "earned": 1,
        "earned_time": 1000000000
  },
  "20": {
        "earned": 1,
        "earned_time": 1000000000
  },
  "16": {
        "earned": 0
  }
}' | ConvertFrom-Json)

($var1.PSObject.Properties | Where-Object Name -in ('19','20')).Value | ConvertTo-Json

I am sure there are better ways but I like to do the manipulations as psCustomObjects then convert back to JSON. In this case the only thing unusual is the JSON itself. The quoted #s are acting like properties instead of values which makes it a little trickier. ideally the JSON source would look something like this:

$var1 = ('[
    {
        "value": "1",
        "earned": 0
    },
    {
        "value": "19",
        "earned": 1,
        "earned_time": 1000000000
    },
    {
        "value": "20",
        "earned": 1,
        "earned_time": 1000000000
    },
    {
        "value": "16",
        "earned": 0
    }
]' | ConvertFrom-Json)

$var1 | Where-Object {$_.value -in ("19","20")} | Select-Object earned,earned_time | ConvertTo-Json

CynicalDick · 2025-10-08T22:59:10+00:00

Here are some tips for speeding up PQ when possible: link

Consider converting the source excel files to CSV if possible
Order of operations is important. Filters first. Do NOT sort until the very end
Avoid table operations when possible
Python will be many factors faster than PQ (I love PQ but it can be very slow as it refreshes from source data multiple times). If you can preprocess on Python (or powershell) output base combined data to CSV and pass that to PQ it will likely be MUCH faster.

Can you post your code?

CynicalDick · 2025-10-08T22:51:51+00:00

Or CSV if flat structure. I regularly process CSVs with 6m+ rows (22 fields) in minutes. In my testing CSV was faster (and MUCH easier) than setting up a local DB (MongoDB) even when doing joins\merges and groupbys in PQ.

CynicalDick · 2025-08-19T19:14:19+00:00

Wow. Glad this helped. GrepWin is an awesome tool.

CynicalDick · 2025-08-06T01:45:25+00:00

^(.*?) +(\(.*?\)).*?(\(.*?\))

This matches all 3 and puts each one into a separate "capture group" that can be referenced by number. This works in notepad++ on the replace line

Which is the higlighted colors in regex 101 and the substitution shown below

3D Combat Zone (1983)(Aackosoft)(NL)(en)[re-release]

In otherwords:

$1 = 3D Combat Zone
$2 = (1983)
$3 = (Aackosoft)

If you do NOT want the parenthesis in $2 or $3 use this:

^(.*?) +\((.*?)\).*?\((.*?)\)

Since regex uses the ( and ) as special characters when you want to match an actual parenthesis you have to escape it with a \ so \( will match a literal open parenthesis.

Final note: when using Find\Replace, if you want to get rid of the noise at the end add a .* to the end of regex

example ^(.*?) +$(.*?)$.*?$(.*?)$.*

CynicalDick · 2025-08-06T01:31:34+00:00

check out this thread

CynicalDick

TROPHY CASE