Question 109046 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSONata to flatten an object with two array values

I am starting with an object having two values that are arrays. For each one I want to match all non-array values with the array values but with the index values in order e.g, index zero from each array, index 1 from each array, etc.:

The object:

{
  "taxedLineItems": [
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265654",
      "lineItemNo": 99274137,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    },
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265654",
      "lineItemNo": 99274137,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    },
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265656",
      "lineItemNo": 99274142,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    },
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265656",
      "lineItemNo": 99274142,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    }
  ]
}

I am wanting to "flatten" it so that all the combinations are expressed with no array values – desired output:

{
  "taxedLineItems": [
    {
      "taxCodeNo": "39050",
      "invoiceLink": "20265654",
      "lineItemNo": 99274137,
      "taxCode": "VAT-A6"
    },
    {
      "taxCodeNo": "39051",
      "invoiceLink": "20265654",
      "lineItemNo": 99274137,
      "taxCode": "Other"
    },
    {
      "taxCodeNo": "39050",
      "invoiceLink": "20265656",
      "lineItemNo": 99274142,
      "taxCode": "VAT-A6"
    },
    {
      "taxCodeNo": "39051",
      "invoiceLink": "20265656",
      "lineItemNo": 99274142,
      "taxCode": "Other"
    }
  ]
}

I have tried and failed with various $map attempts, etc. One failed attempt:

{
"taxedLineItemDetails":
$.taxedLineItems.$map(taxCode, function($v, $i, $a) {
    {
  "invoiceLink":$$.taxedLineItems[$i].invoiceLink,
  "lineItemNo":$$.taxedLineItems[$i].lineItemNo,
  "taxCode":$v  }
})
}

Some advice please!

Tags:

2

Answers


  1. 0

    The following is one option:

    {
    "taxedLineItems":$reduce(
        $map($distinct(taxedLineItems.invoiceLink), function($link, $linkIndex) {
            $map($filter(taxedLineItems, function($v, $i) {$v.invoiceLink = $link}), function($item, $itemIndex) {
                {
                    "taxCodeNo": $item.taxCodeNo[$itemIndex],
                    "invoiceLink": $item.invoiceLink,
                    "lineItemNo": $item.lineItemNo,
                    "taxCode": $item.taxCode[$itemIndex]
                }
            })        
        }), $append)
}

    With the following parts:
    This identifies the unique invoiceLinks:

    $distinct(taxedLineItems.invoiceLink)

    Mapping over those with:

    $map($filter(taxedLineItems, function($v, $i) {$v.invoiceLink = $link})...

    Will allow just dealing with the objects that relate to the invoiceLink.

    Then the $itemIndex is relevant to the arrays for taxCodeNo and taxCode:

    {
    "taxCodeNo": $item.taxCodeNo[$itemIndex],
    "invoiceLink": $item.invoiceLink,
    "lineItemNo": $item.lineItemNo,
    "taxCode": $item.taxCode[$itemIndex]
}

    The wrapper of:

    $reduce(..., $append)

    Is per this link.

    Working example:

    (async function () {

  const transform = `{
      "taxedLineItems":$reduce(
          $map($distinct(taxedLineItems.invoiceLink), function($link, $linkIndex) {
              $map($filter(taxedLineItems, function($v, $i) {$v.invoiceLink = $link}), function($item, $itemIndex) {
                  {
                      "taxCodeNo": $item.taxCodeNo[$itemIndex],
                      "invoiceLink": $item.invoiceLink,
                      "lineItemNo": $item.lineItemNo,
                      "taxCode": $item.taxCode[$itemIndex]
                  }
              })        
          }), $append)
  }`;

  const result = await jsonata(transform).evaluate(obj);
  
  console.log(result);
  
})()
    .as-console-wrapper { max-height: 100% !important; top: 0; }
    <script src="https://cdn.jsdelivr.net/npm/jsonata/jsonata.min.js"></script>
<script>
const obj = {
  "taxedLineItems": [
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265654",
      "lineItemNo": 99274137,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    },
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265654",
      "lineItemNo": 99274137,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    },
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265656",
      "lineItemNo": 99274142,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    },
    {
      "taxCodeNo": [
        "39050",
        "39051"
      ],
      "invoiceLink": "20265656",
      "lineItemNo": 99274142,
      "taxCode": [
        "VAT-A6",
        "Other"
      ]
    }
  ]
}
</script>
    Login or Signup to reply.
  2. 0

    Here’s another solution that will might help:

    $distinct(
  $map($.taxedLineItems, function($v, $i, $a) {
    $map($v.taxCode, function($tax, $idx, $array) {
      {
        "taxCodeNo": $v.taxCodeNo[$idx],
        "invoiceLink": $v.invoiceLink,
        "lineItemNo": $v.lineItemNo,
        "taxCode": $tax
      }
    })
  }) ~> $reduce(function($acc, $val, $i, $a) { $append($acc, $val) }, [])
)

    You can check it out on Stedi’s JSONata Playground: https://stedi.link/W2AWzh9

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search