# Flattening this nested JSON structure?

**URL:** <https://community.parabola.io/t/flattening-this-nested-json-structure/542>\
**Category:** Ask a question\
**Created:** [May 18, 2020, 11:58pm UTC](https://community.parabola.io/t/flattening-this-nested-json-structure/542 "2020-05-18T23:58:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![John\_Doe](https://avatars.discourse-cdn.com/v4/letter/j/91b2a8/32.png) [@John\_Doe](https://community.parabola.io/u/John_Doe)\
**Post date:** [May 18, 2020, 11:58pm UTC](https://community.parabola.io/t/flattening-this-nested-json-structure/542/1 "2020-05-18T23:58:52Z")

</div>

Hello there!

I am attempting to flatten [this JSON output](https://docs.dataforseo.com/v3/keywords_data/google_trends/explore/live/) so that I have this end result table (OK to have extraneous columns).

keywords|values (these are the column names)  
rugby|10  
cricket|60

However, I have only successfully achieved these two unwanted results:

keywords|values (these are the column names)  
rugby|10  
rugby|60  
cricket|10  
cricket|60  
_This example shows incorrect combinations of keywords & values_

keywords|values (these are the column names)  
rugby,cricket|10  
rugby,cricket|60  
_This example shows failure to denote the separate keywords in the result_

Below is the JSON output I am working with. I think the problem might stem from the fact that the ‘keyword’ and ‘values’ are at different “levels of the hierarchy.”

```auto
 "result": [
        {
          "keywords": [
            "rugby",
            "cricket"
          ],
          "type": "trends",
          "location_code": 2840,
          "language_code": "en-US",
          "check_url": "https://trends.google.com/trends/explore?hl=en-US&cat=3&gprop=youtube&geo=US&date=today%2012-m&q=rugby%2Ccricket",
          "datetime": "2020-03-25 11:44:16 +00:00",
          "items_count": 6,
          "items": [
            {
              "position": 1,
              "type": "google_trends_graph",
              "title": "Interest over time",
              "keywords": [
                "rugby",
                "cricket"
              ],
              "data": [
                {
                  "date_from": "2019-03-31",
                  "date_to": "2019-04-06",
                  "timestamp": 1553990400,
                  "values": [
                    10,
                    60
                  ]
                },
                {
                  "date_from": "2019-04-07",
                  "date_to": "2019-04-13",
                  "timestamp": 1554595200,
                  "values": [
                    16,
                    58
                  ]
                },
                {
                  "date_from": "2019-04-14",
                  "date_to": "2019-04-20",
                  "timestamp": 1555200000,
                  "values": [
                    11,
                    55
                  ]
                }
            ],

```

I have tried things like this:

- Set JSON Flattener to flatten the ‘result’ column into ‘api.result \> items \> data’. However, this just gives me the separated data values but fails to show me the separated keywords:

keywords|values  
rugby,cricket|10  
rugby,cricket|60

Do you happen to see where I am going wrong? I would greatly appreciate any solution I may be overlooking. Thank you!

---

<div class="post-metadata">

**Author:** ![brian](https://sea2.discourse-cdn.com/flex020/user_avatar/community.parabola.io/brian/32/11_2.png) [@brian](https://community.parabola.io/u/brian)\
**Post date:** [May 19, 2020, 2:01am UTC](https://community.parabola.io/t/flattening-this-nested-json-structure/542/2 "2020-05-19T02:01:53Z")

</div>

This is a tricky one!

As you pointed out, there are two levels of hierarchy here, and the association that you are trying to enforce is only represented by the order in which things appear in those two arrays you are trying to match up.

So knowing that, we need to process this in two branches and join it back up using the order as the key.

Assuming you are starting with the results array expanded:

1. Use a JSON Flattener to flatten and then expand the `items` array
2. Send the output from that JSON Flattener into two more JSON Flatteners, in parallel branches.
3. In the 2nd JSON Flattener, flatten and expand the `items.keywords` array
4. In the 3rd JSON Flattener, flatten and expand the `items.data.values` array
5. After each JSON Flattener (2 and 3), add a Row Numbers Step
6. Join the two branches together from the Row Numbers steps, using the Row Numbers columns as the keys. Use all default settings in the Join.

That should do it!

 ![image](https://us1.discourse-cdn.com/flex020/uploads/parabola/original/1X/9313dfc14b3e8940eb78e3d79463353c7c44390c.jpeg)

---

<div class="post-metadata">

**Author:** ![John\_Doe](https://avatars.discourse-cdn.com/v4/letter/j/91b2a8/32.png) [@John\_Doe](https://community.parabola.io/u/John_Doe)\
**Post date:** [May 19, 2020, 6:24am UTC](https://community.parabola.io/t/flattening-this-nested-json-structure/542/3 "2020-05-19T06:24:53Z")

</div>

Thanks Brian, this is a brilliant solution. It worked great.

I really appreciate your incredibly fast and thoughtful reply!