convert json with string title array into mysql using powershell

Question

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

user_15339544 · Answer

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

user_724039 · Answer

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

WITH RECURSIVE cte1 AS (
    SELECT 0 as x
    UNION ALL
    SELECT x+1 FROM cte1 WHERE x<10)
SELECt 
   JSON_UNQUOTE(d) d, 
   JSON_EXTRACT(@json,CONCAT("$.options.",d,".c")) c,
   JSON_EXTRACT(@json,CONCAT("$.options.",d,".p")) p
FROM (
   SELECT JSON_EXTRACT( 
       (SELECT JSON_EXTRACT(JSON_KEYS(JSON_EXTRACT(@json, '$.options')),"$[*]")),
       CONCAT("$[",x,"]")) as d
   FROM cte1
   ) xx
WHERE not d is null

This produces:

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

and that's where I stopped....

The DBFIDDLE (for anyone who dares to complete this...)

convert json with string title array into mysql using powershell

2 Answers