Sort and group in xml to json conversion

Question

Am trying to sort and group the lineitems based on 'itemLotNo' in 'Address'. when the lineitems are same in Address then those should form in between the square bracket or Array Address[[{...},{...}],[{...}],[{...}]].

The data should remains same i.e without change in the lineitems only need to sort and group the same 'itemLotNo'.

xmlFile:

<root>
    <FirstName>Alex</FirstName>
    <LastName>Fin</LastName>
    <Details>
        <Id_Number>111</Id_Number>
        <Location>NC</Location>
        <Contact>
            <PhoneNumber>+1 323</PhoneNumber>
        </Contact>
    </Details>
    <Details>
        <Id_Number>222</Id_Number>
        <Location>TX</Location>
        <Contact>
            <PhoneNumber>+1 323</PhoneNumber>
        </Contact>
    </Details>
    <Address>
        <itemLotNo>19949-2018-0001-45116-Dot1</itemLotNo>
        <Locality>Urban</Locality>
        <Type>Mobile</Type>
    </Address>
    <Address>
        <itemLotNo>19950-2018-0001-45116-Dot1</itemLotNo>
        <Locality>Rural</Locality>
        <Type>Landline</Type>
    </Address>
    <Address>
        <itemLotNo>19949-2018-0001-45116-Dot1</itemLotNo>
        <Locality>Rural</Locality>
        <Type>Landline</Type>
    </Address>
    <Address>
        <itemLotNo>19958-2018-0001-45116-Dot1</itemLotNo>
        <Locality>Rural</Locality>
        <Type>Landline</Type>
    </Address>
</root>

ExpectedJsonFile:

{
  "FirstName": "Alex",
  "LastName": "Fin",
  "Details": [
    [
      {
        "Id_Number": "111",
        "Location": "NC",
        "Contact": {
          "PhoneNumber": "+1 323"
        }
      },
      {
        "Id_Number": "222",
        "Location": "TX",
        "Contact": {
          "PhoneNumber": "+1 323"
        }
      }
    ]
  ],
  "Address": [
    [
      {
        "itemLotNo": "19949-2018-0001-45116-Dot1",
        "Locality": "Urban",
        "Type": "Mobile"
      },
      {
        "itemLotNo": "19949-2018-0001-45116-Dot1",
        "Locality": "Rural",
        "Type": "Landline"
      }
    ],
    [
      {
        "itemLotNo": "19950-2018-0001-45116-Dot1",
        "Locality": "Rural",
        "Type": "Landline"
      }
    ],
    [
      {
        "itemLotNo": "19958-2018-0001-45116-Dot1",
        "Locality": "Rural",
        "Type": "Landline"
      }
    ]
  ]
}

xsltCode:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all"
    xmlns="http://www.w3.org/2005/xpath-functions"
    expand-text="yes"
    version="3.0">

  <xsl:output method="text"/>

  <xsl:template match="/">
      <xsl:variable name="json-xml">
          <xsl:apply-templates/>
      </xsl:variable>
      <xsl:value-of select="xml-to-json($json-xml, map { 'indent' : true() })"/>
  </xsl:template>
  
  <xsl:template match="*[not(*)]">
    <string key="{local-name()}">{.}</string>
  </xsl:template>
  
  <xsl:template match="*[(*) and . castable as xs:double]">
    <number key="{local-name()}">{.}</number>
  </xsl:template>
  
  <xsl:template match="*[*]">
    <xsl:param name="key" as="xs:boolean" select="false()"/>
    <map>
      <xsl:if test="$key">
        <xsl:attribute name="key" select="local-name()"/>
      </xsl:if>
      <xsl:for-each-group select="*" group-by="node-name()">
          <xsl:choose>
              <xsl:when test="current-group()[2] or self::Details or self::Address">
                  <array key="{local-name()}">
                    <xsl:choose>
                      <xsl:when test="self::Details">
                        <array>
                          <xsl:apply-templates select="current-group()">
                            <xsl:with-param name="key" select="false()"/>
                          </xsl:apply-templates>                        
                        </array>
                      </xsl:when>
<xsl:when test="self::Address">
                        <xsl:iterate select="current-group()">
<array>
                          <xsl:apply-templates select="self::Address">

                          </xsl:apply-templates>                        
                        </array>
                        </xsl:iterate>
                      </xsl:when>
                      <xsl:otherwise>
                        <xsl:apply-templates select="current-group()">
                          <xsl:with-param name="key" select="false()"/>
                        </xsl:apply-templates>
                      </xsl:otherwise>                      
                    </xsl:choose>
                  </array>
              </xsl:when>
              <xsl:otherwise>
                  <xsl:apply-templates select="current-group()">
                    <xsl:with-param name="key" select="true()"/>
                  </xsl:apply-templates>
              </xsl:otherwise>
          </xsl:choose>
      </xsl:for-each-group>
    </map>
  </xsl:template>

</xsl:stylesheet>

Martin Honnen · Accepted Answer · 2021-05-27 10:54:22Z

With the special treatment of Address it seems you want

  <xsl:template match="*[*]">
    <xsl:param name="key" as="xs:boolean" select="false()"/>
    <map>
      <xsl:if test="$key">
        <xsl:attribute name="key" select="local-name()"/>
      </xsl:if>
      <xsl:for-each-group select="*" group-by="node-name()">
          <xsl:choose>
              <xsl:when test="current-group()[2] or self::Details or self::Address">
                  <array key="{local-name()}">
                    <xsl:choose>
                      <xsl:when test="self::Details">
                        <array>
                          <xsl:apply-templates select="current-group()">
                            <xsl:with-param name="key" select="false()"/>
                          </xsl:apply-templates>                        
                        </array>
                      </xsl:when>
                      <xsl:when test="self::Address">
                        <xsl:for-each-group select="current-group()" group-by="itemLotNo">
                          <array>
                            <xsl:apply-templates select="current-group()">
                              <xsl:with-param name="key" select="false()"/>
                            </xsl:apply-templates>
                          </array>
                        </xsl:for-each-group>
                      </xsl:when>
                      <xsl:otherwise>
                        <xsl:apply-templates select="current-group()">
                          <xsl:with-param name="key" select="false()"/>
                        </xsl:apply-templates>
                      </xsl:otherwise>                      
                    </xsl:choose>
                  </array>
              </xsl:when>
              <xsl:otherwise>
                  <xsl:apply-templates select="current-group()">
                    <xsl:with-param name="key" select="true()"/>
                  </xsl:apply-templates>
              </xsl:otherwise>
          </xsl:choose>
      </xsl:for-each-group>
    </map>
  </xsl:template>

.....Thank you very much for the support Martin...Its working as expected.
@raviteja, consider to mark the answer as accepted if your problem is solved.

Siebe Jongebloed · Accepted Answer · 2021-05-27 10:01:02Z

If you change the last template to this:

  <xsl:template match="*[*]">
    <xsl:param name="key" as="xs:boolean" select="false()"/>
    <map>
      <xsl:if test="$key">
        <xsl:attribute name="key" select="local-name()"/>
      </xsl:if>
      <xsl:for-each-group select="*" group-by="node-name()">
        <xsl:choose>
          <xsl:when test="current-group()[2] or self::Details or self::Address">
            <array key="{local-name()}">
              <xsl:choose>
                <xsl:when test="self::Details">
                    <xsl:apply-templates select="current-group()">
                      <xsl:with-param name="key" select="false()"/>
                    </xsl:apply-templates>                        
                </xsl:when>
                <xsl:when test="self::Address">
                  <xsl:iterate select="current-group()">
                    <xsl:apply-templates select="self::Address"/>
                  </xsl:iterate>
                </xsl:when>
                <xsl:otherwise>
                  <xsl:apply-templates select="current-group()">
                    <xsl:with-param name="key" select="false()"/>
                  </xsl:apply-templates>
                </xsl:otherwise>                      
              </xsl:choose>
            </array>
          </xsl:when>
          <xsl:otherwise>
            <xsl:apply-templates select="current-group()">
              <xsl:with-param name="key" select="true()"/>
            </xsl:apply-templates>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:for-each-group>
    </map>
  </xsl:template>

You will get this json:

{
  "FirstName": "Alex",
  "LastName": "Fin",
  "Details": [
    {
      "Id_Number": "111",
      "Location": "NC",
      "Contact": {"PhoneNumber": "+1 323"}
    },
    {
      "Id_Number": "222",
      "Location": "TX",
      "Contact": {"PhoneNumber": "+1 323"}
    }
  ],
  "Address": [
    {
      "itemLotNo": "19949-2018-0001-45116-Dot1",
      "Locality": "Urban",
      "Type": "Mobile"
    },
    {
      "itemLotNo": "19950-2018-0001-45116-Dot1",
      "Locality": "Rural",
      "Type": "Landline"
    },
    {
      "itemLotNo": "19949-2018-0001-45116-Dot1",
      "Locality": "Rural",
      "Type": "Landline"
    },
    {
      "itemLotNo": "19958-2018-0001-45116-Dot1",
      "Locality": "Rural",
      "Type": "Landline"
    }
  ]
}

That is not the same as your ExpectedJsonFile, since it has not the nested array for Address and Details, but maybe it is even better. See json question about array of objects.

Collectives™ on Stack Overflow

Sort and group in xml to json conversion

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related