Work with JSON in BigQuery with dynamic keys

Question

I have a json field in BigQuery called json_response. It has this shape:

{
    "2024-11-24": {
        "fatturato": {
            "numero": 281,
            "totale": 44444.72,
            "paesi": {
                "ITA": {
                    "num": 84,
                    "val": 33333.38
                },
                "USA": {
                    "num": 60,
                    "val": 20222.14
                }
            }
        },
        "ordini": {
            "totale": 5555.67,
            "num": 7,
            "paesi": {
                "ITA": {
                    "num": 14,
                    "val": 55.7
                },
                "USA": {
                    "num": 16,
                    "val": 222.5
                }
            }
        }
    },
    "2024-11-25": {
        "fatturato": {
            "numero": 225,
            "totale": 33333.72,
            "paesi": {
                "ITA": {
                    "num": 25,
                    "val": 22222.38
                },
                "USA": {
                    "num": 60,
                    "val": 20222.14
                }
            }
        },
        "ordini": {
            "totale": 5555.67,
            "num": 7,
            "paesi": {
                "ITA": {
                    "num": 14,
                    "val": 55.7
                },
                "USA": {
                    "num": 16,
                    "val": 222.5
                }
            }
        }
    },
}

I'd like to query it in order to have a column called ref_date (with the date in the keys, which can be many), a column "paesi", a column "fatturato_numero", "fatturato_valore" etcetera, ignoring the totals. In this case I'll have two rows, one for "2024-11-24" and the other for " "2024-11-25"".

I tried using JSON_EXTRACT, but it says that keys must be constant, not dynamic.

Answer 1

You can use JSON_KEYS function to get all the keys from the JSON and then access theirs respective values:

SELECT 
    ref_date,
    json_column[ref_date].fatturato.paesi as paesi,
    json_column[ref_date].fatturato.numero as fatturato_numero
FROM sample, UNNEST(JSON_KEYS(sample.json_column, 1)) as ref_date

Output: