CODEWITHSUNDEEP
jsonata
Jon Wright
Jon Wright

Reputation: 57

How to use JSONata to change JSON response

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.

Upvotes: 1

Views: 557

Answers (1)

Rafal Wiliński
Rafal Wiliński

Reputation: 2390

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.

Mappings UI

Upvotes: 8

Related Questions