如何在PandasSql中读取嵌套的Json文件

import pandas as pd
from pandasql import sqldf

data = pd.read_json('file.json')

df = pd.json_normalize(data)

pysqldf = lambda q: sqldf(q, globals())

query = '''
WITH RECURSIVE flatten AS (
  SELECT
    id,
    json_extract_scalar(json_column, '$.key') AS key,
    json_extract_scalar(json_column, '$.value') AS value
  FROM
    df
  UNION ALL
  SELECT
    id,
    json_extract_scalar(json_column, '$.key') AS key,
    json_extract_scalar(json_column, '$.value') AS value
  FROM
    flatten,
    json_each(json_extract(json_column, '$.nested_json'))
)
SELECT
  id,
  key,
  value
FROM
  flatten
'''

result = pysqldf(query)