Solved

Pivot stage on empty json list

  • 26 April 2022
  • 5 replies
  • 182 views

 I have created the simple SDC pipeline with json as input data.
When i pivot the list item from json, it works fine when list has the elements but its eliminates the records when list is empty
Pivot on revision works but requirements does not produce record
Appreciate any help . Thanks in advance.
 {
            "id": 22,
            "active": true,
            "code": "210_34",
            "productcomponents": [],
            "category": {
                "id": 21,
                "code": "SOP",
                "name": "Standard Operating Procedures"
            },
            "revisions": [
                {
                    "id": 27,
                    "active": true,
                    "created": "2020-08-19 15:45:18"
                }
            ],
            "requirements": []
}

icon

Best answer by mikelsr 26 April 2022, 23:19

View original

5 replies

Hi,

 

According to the documentation [1] the Pivot Field stage creates one record per item in the list or map you are pivoting.

When you pivot a field, the Field Pivoter creates a new record for each first-level item in the list or map.

The list being pivoted in the case of `/requirements` has no items, so no records are created.

 

Feel free to reply if I did not get the question right or if you are looking for some other functionality, in which case we can look for another pipeline design to achieve it.

 

[1] https://docs.streamsets.com/portal/platform-datacollector/latest/datacollector/UserGuide/Processors/ListPivoter.html#GeneratedRecords

Thank you for the quick response

I was expecting record with only with non pivoted fields. What is the alternative solution for this situation when list is empty.

If I understood correctly, perhaps we could add another stage to filter out the pivoted fields, and then merge the output records of that stage and the pivot stage like in the following image.

Pipeline overview

If both stages filter the `/revisions` field you get the following input records in the destination stage:

Combined output of Field Pivoter and Field Remover

 

Is this closer to what you expected? You can find the exported pipeline attached to this message.

 

Regards

Userlevel 5
Badge

@meghraj was @mikelsr suggestion helpful? If so, Please select  “Best answer” :) 

Userlevel 5
Badge

@meghraj I have made Mikes suggestion the best answer. Thanks of for letting me know! 

Reply