[jira] [Created] (FLINK-26818) Required Information on parsing complex nested JSON using SQL API

["Arindam Bhattacharjee (Jira)" <ji...@apache.org>]

Arindam Bhattacharjee created FLINK-26818:
---------------------------------------------

             Summary: Required Information on parsing complex nested JSON using SQL API
                 Key: FLINK-26818
                 URL: https://issues.apache.org/jira/browse/FLINK-26818
             Project: Flink
          Issue Type: Bug
          Components: Table SQL / API
    Affects Versions: 1.11.6
         Environment: PROD
            Reporter: Arindam Bhattacharjee


Hi Team,

I need guidance on DDL and DML for nested JSON parsing using FLINK SQL API. The sample JSON payload is given below - 
{code:java}
{
"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
            }
        ]
    }
  ]
} {code}
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



--
This message was sent by Atlassian Jira
(v8.20.1#820001)