Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
data = {
 "level1": {
 "rows": [
 {"col1": 1, "col2": 2},
 ]
 },
 "meta1": {
 "meta_sub1": 1,
 }, 
}
df = pd.json_normalize(data, record_path=["level1", "rows"], meta=["meta1"])
print(df)
df = pd.json_normalize(
 data,
 record_path=["level1", "rows"],
 meta=[["meta1", "meta_sub1"]], # Trying to access sub-fields within meta1
)

Issue Description

Description of the Issue

This reproducible example demonstrates the challenges and potential pitfalls when using pandas.json_normalize() to extract and flatten hierarchical data structures with nested metadata:

Data Structure

The data dictionary is multi-layered, with nested dictionaries and a list of dictionaries (rows) under level1. Additionally, meta1 is structured as a dictionary containing subfields.

Successful Normalization

The first call to pd.json_normalize() extracts the data from rows under level1 and includes meta1as a top-level metadata field. This works as intended becausemeta1 is accessed directly as a single key.

Output:

 col1 col2 meta1
0 1 2 {'meta_sub1': 1}

KeyError with Nested Meta Fields

The second pd.json_normalize() call attempts to extract subfields from meta1 using a nested path (meta=[["meta1", "meta_sub1"]]). This results in a KeyError because json_normalize() does not natively support nested lists for specifying paths within the meta parameter.

Expected Behavior

df = pd.json_normalize(
 data,
 record_path=["level1", "rows"],
 meta=[["meta1", "meta_sub1"]], # Trying to access sub-fields within meta1
)

 col1 col2 meta1
0 1 2 1

Installed Versions