'convert json with string title array into mysql using powershell

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

mysqlarraysjsonpowershell


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk

Related Questions