Forum Discussion

XanderVR's avatar
XanderVR
New Contributor
2 months ago
Solved

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.

expression_library
json
  • SpiroTaleski's avatar
    SpiroTaleski
    2 months ago

    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

3 Replies

  • Scott's avatar
    Scott
    Admin
    2 months ago

    Hi XanderVR - Can you take your question here and copy to SnapGPT, accessible in the Designer? It should generate a pipeline with expressions that get you what you are looking for.

     

    • XanderVR's avatar
      XanderVR
      New Contributor
      2 months ago

      I have tried that indeed, but it comes nowhere close to a solution.
      Every expression it generates in the mapper snaps are returning null values

      I managed to build a pipeline that completes this task, but since I am quite new to SnapLogic there might be better ways (ie. using less snaps) to achieve this result with better performance.

  • SpiroTaleski's avatar
    SpiroTaleski
    Valued Contributor
    2 months ago

    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