Generate expression file from database query
For some data transformations I would like to use an expression file that is generated each night, instead of querying a SQL database everytime the pipeline is started.
I already have data available in the database and now I need to get the data transformed in the expression file JSON format, but I am stuck on getting the right ouput.
Coming from a XML oriented environment (with extensive knowledge in XSL but not so much JSON) I have quite some issues with switching to snaps and JSON...
Data sample (JSON) from the database
[
{
"code": "ARTICLEGROUP",
"source": "JLG",
"target": "10"
},
{
"code": "COMMODITYCODE",
"source": "31251501",
"target": "0"
},
{
"code": "COUNTRYCODE",
"source": "AF",
"target": "AF"
},
{
"code": "COUNTRYCODE",
"source": "AL",
"target": "AL"
},
{
"code": "COUNTRYCODE",
"source": "DZ",
"target": "DZ"
},
{
"code": "COUNTRYCODE",
"source": "AS",
"target": "AS"
},
{
"code": "COUNTRYCODE",
"source": "AD",
"target": "AD"
},
{
"code": "COUNTRY_ISOCODE",
"source": "ARE",
"target": "AE"
},
{
"code": "COUNTRY_ISOCODE",
"source": "AFG",
"target": "AF"
},
{
"code": "COUNTRY_ISOCODE",
"source": "ALA",
"target": "AX"
},
{
"code": "COUNTRY_ISOCODE",
"source": "ALB",
"target": "AL"
},
{
"code": "UOM",
"source": "EA",
"target": "pi"
},
{
"code": "UOM",
"source": "M",
"target": "me"
},
{
"code": "UOM",
"source": "BG",
"target": "za"
}
]
Desired output
{
"ARTICLEGROUP" : {
"JLG": "10"
},
"COMMODITYCODE" : {
"31251501": "0"
},
"COUNTRYCODE" : {
"AF": "AF",
"AL": "AL",
"DZ": "DZ",
"AS": "AS",
"AD": "AD"
},
"COUNTRY_ISOCODE" : {
"ARE": "AE",
"AFG": "AF",
"ALA": "AX",
"ALB": "AL"
},
"UOM" : {
"EA": "pi",
"M": "me",
"BG": "za"
}
, getValue : (type, source) => this[type][source]
}
Anyone can point me in the right direction?
Have tried multiple things already, but I can't get the "arrays" right for some reason.
XanderVR Achieving the desired output (without the getValue function) can be done using the reduce function.
First you need to group the records by $code, using Group By Fields snap. After that following expression can be used:
{[$groupBy.code]:$group.reduce((acc, curr) => acc.extend({[curr.source]: curr.target}), {})}BR,
Spiro
