[FLINK-26818] Required Information on parsing complex nested JSON using SQL API

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Information Provided
• Affects Version/s: 1.11.6
• Component/s: Table SQL / API
• Labels: json json_array sql-api stale-major

Environment

PROD

• Assignee: Unassigned
• Reporter: Arindam Bhattacharjee
• Created: 23/Mar/22 07:55
• Updated: 05/Jul/22 10:41
• Resolved: 05/Jul/22 10:41

Description

Hi Team,

I need guidance on DDL and DML for nested JSON parsing using FLINK SQL API. The sample JSON payload is given below - 

{
"success": true,
"payload": [
    {
        "weekNumber": 40,
        "sortOrder": 1,
        "label": "autumn",
        "numberOfPossibleDays": 3,
        "editable": true,
        "selectedDate": "2020-09-29",
        "deliveryDays": [
            {
                "date": "2020-09-28",
                "contactPerson": null,
                "phoneNumber": null,
                "contactPerson2": null,
                "phoneNumber2": null,
                "selected": false
            },
            {
                "date": "2020-09-29",
                "contactPerson": "John",
                "phoneNumber": "99887744",
                "contactPerson2": "Tom",
                "phoneNumber2": "40040000,
                "selected": true
            },
            {
                "date": "2020-09-30",
                "contactPerson": null,
                "phoneNumber": null,
                "contactPerson2": null,
                "phoneNumber2": null,
                "selected": false
            }
        ]
    },
    {
        "weekNumber": 53,
        "sortOrder": 2,
        "label": "christmas",
        "numberOfPossibleDays": 2,
        "editable": true,
        "selectedDate": "2020-12-29",
        "deliveryDays": [
            {
                "date": "2020-12-28",
                "contactPerson": null,
                "phoneNumber": null,
                "contactPerson2": null,
                "phoneNumber2": null,
                "selected": false
            },
            {
                "date": "2020-12-29",
                "contactPerson": "Doe,
                "phoneNumber": "99999999",
                "contactPerson2": "Foo",
                "phoneNumber2": "44552200",
                "selected": true
            }
        ]
    }
  ]
} 

I want to know what would be the DDL for this JSON structure and how can I select the particular fields using SELECT statement. 

 

Thanks in advance and it will be very helpful for me.

 

-

Arindam