1

I have a input json in the format

[
{
    "PERSON_ID": 78,
    "EFFECTIVE_START_DATE": "2013-12-02 00:00:00",
    "LAST_NAME": "Hulk78"
},
{
    "PERSON_ID": 78,
    "EFFECTIVE_START_DATE": "2020-06-24 07:29:26",
    "LAST_NAME": "Hulks78"
},
{
    "PERSON_ID": 79,
    "EFFECTIVE_START_DATE": "2015-12-02 00:00:00",
    "LAST_NAME": "Hulk79"
},
{
    "PERSON_ID": 79,
    "EFFECTIVE_START_DATE": "2020-07-24 07:29:26",
    "LAST_NAME": "Hulks79"
},
{
    "PERSON_ID": 80,
    "EFFECTIVE_START_DATE": "2013-12-10 00:00:00",
    "LAST_NAME": "Hulk15"
}

]

The expected output

[
{
    "PersonId": 78,
    "value": [
        {
            "EffectiveDate": "2013-12-02 00:00:00",
            "lastName":"Hulk78"
        },
        {
            "EffectiveDate": "2020-06-24 07:29:26",
            "lastName":"Hulks78"
        }
    ]
}
....

]

I want to transform the input json by grouping the person_id value and for each group add its respective effectiveDate and lastname in a array of values corresponding to that person id. Below is the xslt that i have tried.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema" version="3.0"
xmlns="http://www.w3.org/2005/xpath-functions" xpath-default-namespace="http://www.w3.org/2005/xpath-functions" expand-text="yes">
<xsl:param name="input"/>
<xsl:output method="text"/>

<xsl:template name="xsl:initial-template">
    <xsl:variable name="input-as-xml" select="json-to-xml($input)"/>
    <xsl:variable name="transformed-xml" as="element(array)">
        
      <array>
          <xsl:for-each-group select="$input-as-xml" group-by="//number[@key='PERSON_ID']">
              <map>
                  <string key="PersonId">
                      <xsl:value-of select="current-grouping-key()"/>
                  </string>
                 
                  <array key="Value">
                      <xsl:for-each select="current-group()">
                      <map>
                          <string key="EffectiveDate">
                              <xsl:value-of select="../string[@key='EFFECTIVE_START_DATE']"/>
                          </string>
                          <string key="LASTNAME">
                              <xsl:value-of select="@LAST_NAME"/>
                          </string>
                      </map>
                          
                  </xsl:for-each> 
                  </array>
              </map>
              
          </xsl:for-each-group>
      </array>       
    </xsl:variable>
    <xsl:value-of select="xml-to-json($transformed-xml)"/>
</xsl:template>

</xsl:stylesheet>

Can anyone please help me in understanding how to take out each effective date and last name from the current group.

This is the output i am getting

[
{
    "PersonId": "78",
    "Value": [
        {
            "EffectiveDate": "",
            "LASTNAME": ""
        }
    ]
},
{
    "PersonId": "79",
    "Value": [
        {
            "EffectiveDate": "",
            "LASTNAME": ""
        }
    ]
},
{
    "PersonId": "80",
    "Value": [
        {
            "EffectiveDate": "",
            "LASTNAME": ""
        }
    ]
}

]

prateeknaik
  • 71
  • 1
  • 7
  • Can you please show us the output file in JSON that you generate? – Toolbox May 25 '21 at 07:49
  • [{"PersonId":"78","Value":[{"EffectiveDate":"","LASTNAME":""}]},{"PersonId":"79","Value":[{"EffectiveDate":"","LASTNAME":""}]},{"PersonId":"80","Value":[{"EffectiveDate":"","LASTNAME":""}]}] – prateeknaik May 25 '21 at 07:53
  • This the output i am getting... i am unable to add the effectivedata and lastname value to it. – prateeknaik May 25 '21 at 07:54
  • Please add the output json in the question (prior to expected output, so we see it structured – Toolbox May 25 '21 at 07:54

2 Answers2

2

You were close. This is how to build the variable:

<xsl:variable name="transformed-xml" as="element()">
  <array>
    <xsl:for-each-group select="$input-as-xml/array/map" group-by="number[@key='PERSON_ID']">
      <map>
        <string key="PersonId">
          <xsl:value-of select="current-grouping-key()"/>
        </string>
        <array key="Value">
          <xsl:for-each select="current-group()">
            <map>
              <string key="EffectiveDate">
                <xsl:value-of select="string[@key='EFFECTIVE_START_DATE']"/>
              </string>
              <string key="LASTNAME">
                <xsl:value-of select="string[@key='LAST_NAME']"/>
              </string>
            </map>
          </xsl:for-each> 
        </array>
      </map>          
    </xsl:for-each-group>
  </array>       
</xsl:variable>
Siebe Jongebloed
  • 3,906
  • 2
  • 14
  • 19
1

Note that, as an alternative approach, you can also transform the JSON directly as XDM 3.1 maps and arrays and do the grouping on that data, without the need to convert to XML back and forth; the only drawback is that XSLT 3 lacks an instruction to create an array so there you have to rely on XPath 3.1 expressions like [ ] or array { } which sometimes requires you to use a function call inside a template.

An example for your sample would be

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:mf="http://example.com/mf"
  exclude-result-prefixes="#all"
  expand-text="yes">
  
  <xsl:function name="mf:group" as="map(*)*">
    <xsl:param name="persons" as="map(*)*"/>
    <xsl:for-each-group select="$persons" group-by="?PERSON_ID">
      <xsl:sequence select="map { 'PersonId' : current-grouping-key(), 
                                  'value' : array { 
                                               current-group() ! map { 'EffectiveDate ' : ?EFFECTIVE_START_DATE, 
                                                                        'lastName' : ?LAST_NAME } } }"/>
    </xsl:for-each-group>
  </xsl:function>

  <xsl:output method="json" indent="yes"/>

  <xsl:param name="json-input" as="xs:string"/>

  <xsl:template name="xsl:initial-template">
    <xsl:apply-templates select="parse-json($json-input)"/>
  </xsl:template>

  <xsl:template match=".">
    <xsl:sequence select="array { mf:group(?*) }"/>
  </xsl:template>

</xsl:stylesheet>

Another disadvantage to the JSON -> XML -> JSON conversion is the lack of order of the key value pairs in XDM maps, that way the serialized JSON often doesn't have the order of keys you expect. The commercial versions of Saxon have some extension attribute to define the order for serialization.

With xslt3 of Saxon JS 2.2 and later you can even feed the JSON directly as the input to the transform with the -json:data.json option and use e.g.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="3.0"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:mf="http://example.com/mf"
  exclude-result-prefixes="#all"
  expand-text="yes">
  
  <xsl:function name="mf:group" as="map(*)*">
    <xsl:param name="persons" as="map(*)*"/>
    <xsl:for-each-group select="$persons" group-by="?PERSON_ID">
      <xsl:sequence select="map { 'PersonId' : current-grouping-key(), 
                                  'value' : array { 
                                               current-group() ! map { 'EffectiveDate ' : ?EFFECTIVE_START_DATE, 
                                                                        'lastName' : ?LAST_NAME } } }"/>
    </xsl:for-each-group>
  </xsl:function>

  <xsl:output method="json" indent="yes"/>

  <xsl:template match=".">
    <xsl:sequence select="array { mf:group(?*) }"/>
  </xsl:template>

</xsl:stylesheet>

I think we will see a similar option for the next mayor Saxon Java release.

Martin Honnen
  • 160,499
  • 6
  • 90
  • 110