Json Data Cleaning

[lalitkgehlot89]

Hi,

I need help to clean the nested json data. Sample data file are attcahed.

Required output format is mentioned below.

BRANCH_ID

assessmentYear

salesGrossReceiptsOFBusiness

[Kushal_Chawda]

@lalitkgehlot89  Is the branch_id unique for each attribute_key and attribute_value?

[lalitkgehlot89]

Yes branch_id is unique.

[Kushal_Chawda · Accepted solution]

@lalitkgehlot89  In your json data you don't have Branch_Id which means you may need to loop over all branch and get the json data and process it. Otherwise it would have been easy without loop, if branch_id was part of json data. 

Data:
LOAD
    ATTRIB_KEY,
    BRANCH_ID,
    ATTRIB_VALUE
FROM [lib://Data/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Expanded_json:
Load * Inline [
Junk ];

for Each vBranch in FieldValueList('BRANCH_ID')

Branch:
Load ATTRIB_VALUE as JsonText
Resident Data
where BRANCH_ID='$(vBranch)';

Concatenate(Expanded_json)
Load *,
     '$(vBranch)' as BRANCH_ID
From_Field
(Branch,JsonText)(json,utf8,no labels);

Drop Table Branch;

Next vBranch

Drop Field Junk;

if you don't want json data to be segregated by branch id then you can simply do below

Data:
LOAD
         ATTRIB_KEY,
        BRANCH_ID,
        ATTRIB_VALUE
FROM [lib://SharedFolder_ChADS/Data/Files/Json/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Expanded_json:
Load *
From_Field
(Data,ATTRIB_VALUE)(json,utf8,no labels);