How to use JSONata to change JSON response

Question

I am working with an API that returns the following JSON:

{
  "rows": [
    {
      "keys": [
        "search term 1",
        "https://example.com/article-about-keyword-1/"
      ],
      "clicks": 24,
      "impressions": 54,
      "ctr": 0.4444444444444444,
      "position": 2.037037037037037
    },
    {
      "keys": [
        "search term 2",
        "https://example.com/article-about-keyword-2/"
      ],
      "clicks": 17,
      "impressions": 107,
      "ctr": 0.1588785046728972,
      "position": 2.663551401869159
    }
],
  "responseAggregationType": "byPage"
}

And I'm trying to use JSONata to change it to something more like this:

 {
  "rows": [
    {
      "keyword": search term 1,
      "URL": https://example.com/article-about-keyword-1/,
      "clicks": 24,
      "impressions": 54,
      "ctr": 0.4444444444444444,
      "position": 2.037037037037037
    },
    {
      "keyword": search term 2,
      "URL": https://example.com/article-about-keyword-2/,
      "clicks": 17,
      "impressions": 107,
      "ctr": 0.1588785046728972,
      "position": 2.663551401869159
    }
],
  "responseAggregationType": "byPage"
}

Basically, I'm trying the break the 'keys' part out into 'Keyword' and 'URL'.

Have been playing around for a while in https://try.jsonata.org/ but I'm not getting very far. Any help appreciated.

user_5267551 · Answer

Splitting the keys array should be achievable by accessing each element there by its index (given that the keyword and the URL are guaranteed to appear on the same index).

Here’s the full JSONata expression to translate from your source file to the desired target shape:

{
  "rows": rows.{
    "keyword": keys[0],
    "URL": keys[1],
    "clicks": clicks,
    "impressions": impressions,
    "ctr": ctr,
    "position": position 
  }[],
  "responseAggregationType": responseAggregationType
}

By the way, I’ve built this solution in 2 minutes by using the Mappings tool that my team is building at Stedi.

How to use JSONata to change JSON response

1 Answers