background: I'm trying to loop through this json example and insert the values into a mysql database. The issue I run into is that they use the dates and values for the array headers as a string.

Example Json:

{
    "options":
    {
        "2022-03-04":
        {
            "c":
            {
                "200.00":
                {
                    "oi": 0,
                    "l": 635,
                    "b": 636.85,
                    "a": 640.2,
                    "v": 10
                },
                "250.00":
                {
                    "oi": 2,
                    "l": 594.1,
                    "b": 586.85,
                    "a": 590.2,
                    "v": 5
                }
            },
            "p":
            {
                "200.00":
                {
                    "oi": 2150,
                    "l": 0.03,
                    "b": 0,
                    "a": 0.01,
                    "v": 11
                },
                "250.00":
                {
                    "oi": 518,
                    "l": 0.01,
                    "b": 0,
                    "a": 0.01,
                    "v": 0
                }
            }
        },
        "2022-03-11":
        {
            "c":
            {
                "200.00":
                {
                    "oi": 0,
                    "l": 631.65,
                    "b": 637.2,
                    "a": 639.85,
                    "v": 1
                },
                "250.00":
                {
                    "oi": 5,
                    "l": 596.7,
                    "b": 587.2,
                    "a": 589.85,
                    "v": 5
                }
            },
            "p":
            {
                "200.00":
                {
                    "oi": 1138,
                    "l": 0.01,
                    "b": 0,
                    "a": 0.01,
                    "v": 39
                },
                "250.00":
                {
                    "oi": 371,
                    "l": 0.01,
                    "b": 0,
                    "a": 0.01,
                    "v": 65
                }
            }
        }
    }
}

Example code:

$stock = @(get-content -path ./stock_symbol.json | convertfrom-json) If I do that the resulting array length is consider 1 as it seem the double quota for the data and value of 'c' or 'p' (200 or 250) seem to be consider a string.

Normally I could do a while loop through the array or specific the first or second with $stock.options[0] or $stock.options[1] but both of those command will just spit out the entire option array section

array output

Goal I would like to be able to pull the expiration date, type (c/p), strike (value), bid (b), and ask (a).

This would be an example of what I would like inserted in the database:

result in spreadsheet view

any help/tips are appreciated

CodePudding user response：

This is a pretty complex Json, here is how you can get the information you're looking for, as you can see this requires many nested loops:

$table = foreach($a in $json.options.PSObject.Properties) {
    $expiration = $a.Name
    foreach($b in $a.Value.PSObject.Properties) {
        $type = $b.Name
        foreach($c in $b.Value.PSObject.Properties) {
            $strike = $c.Name
            $bid = $c.Value.b
            $ask = $c.Value.a

            [pscustomobject]@{
                Symbol     = 'Stock' # => This is not on the Json!
                Expiration = [datetime]$expiration
                Type       = ('Call', 'Pull')[$type -eq 'p']
                Strike     = [decimal]$price
                Bid        = [decimal]$bid
                Ask        = [decimal]$ask
            }
        }
    }
}

$table | Format-Table -AutoSize

This would result into a flatten array of objects that can be easily exported to CSV / inserted to SQL tables.

Symbol Expiration            Type Strike    Bid    Ask
------ ----------            ---- ------    ---    ---
Stock  3/4/2022 12:00:00 AM  Call    250 636.85  640.2
Stock  3/4/2022 12:00:00 AM  Call    250 586.85  590.2
Stock  3/4/2022 12:00:00 AM  Pull    250      0   0.01
Stock  3/4/2022 12:00:00 AM  Pull    250      0   0.01
Stock  3/11/2022 12:00:00 AM Call    250  637.2 639.85
Stock  3/11/2022 12:00:00 AM Call    250  587.2 589.85
Stock  3/11/2022 12:00:00 AM Pull    250      0   0.01
Stock  3/11/2022 12:00:00 AM Pull    250      0   0.01

CodePudding user response：

Trying to do it in MySQL fails (only on my site?