Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Parsing out JSON query

Hello,   I'm having a difficult time parsing out 2 rows of lists from a web api. The dates exist in the header lists & the values under the data. How can I merge them together and expand in 1 table...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous ,

    Yes, the returned text would have been what I needed. But of course it works with the API.

    This is a really bad format..

    Please check it out:

     

    let
url = "https://api.socialbakers.com/1/aggregated-metrics",
body = "{
""profiles"": [{""id"": ""36144455"",""platform"":""twitter""}],
""date_start"": ""2020-07-01"",
""date_end"": ""2020-09-30"",
""metric"": ""insights_impressions"",
""dimensions"": [{""type"": ""date.day""}]
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[Headers=[#"Content-Type"="application/json; charset=utf-8",
Authorization="Basic ""TWpReU1EUXlYekU0TmpreE9EaGZNVFU1TkRJeE16a3hNakU0T1Y4MllqZGtZemc1WW1Rd1lqbGxNMkUxWkdRelptRTVaREF3TnpobVpEVTRZUT09OmJjZDRlNGNmMDllMGM0M2IxNjRiYjFiMTdjNjM5ZDE3"""],
Content = Text.ToBinary(body)])),
    ParsedData = Source[data],
    Custom1 = Source,
    header = Custom1[header],
    #"Converted to Table" = Table.FromList(header, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "rows"}, {"type", "rows"}),
    ParsedMetric = #"Expanded Column1"{[type = "metric"]}[rows]{0},
    ParsedDateDays = #"Expanded Column1"{[type = "date.day"]}[rows],
    Custom2 = Table.FromColumns( { ParsedDateDays, ParsedData }, {"DateDays", "Data"}),
    #"Expanded Data" = Table.ExpandListColumn(Custom2, "Data"),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Metric", each ParsedMetric)
in
    #"Added Custom"

     

smarguin's avatar
smarguin
Advocate I
6 years ago

Hello

 

This is brilliant thanks!

 

I would like to build on this and add the detail by social profile. I have added this.

 

Parsed_JSON = Json.Document(body),

profiles1 = Parsed_JSON[profiles],
#"Converted to Table" = Table.FromList(profiles1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "platform"}, {"id", "platform"}),

 

It gives me the list of accounts. Now how do I link the accounts I post to the performance results of the query? Is it actually possible? 

 

I looked at the API (V2) and the response is supposed to provide the accounts as an answer but I dont see them anywhere...

Example request

POST /2/aggregated-metrics HTTPS
Host: api.socialbakers.com
Authorization: Basic base64_encoded_auth
Content-Type: application/json; charset=utf-8

{
  "profiles": [
    {
      "id": "564919357",
      "platform": "twitter",
    },
    {
      "id": "164929129743",
      "platform": "facebook"
    }
  ],
  "date_start": "2018-11-01",
  "date_end": "2018-11-12",
  "metric": "page_posts",
  "dimensions": [
    {
      "type": "profile"
    }
  ],
  "filter": [
    {
      "field": "post_labels",
      "value": [
        "e3af7de2d2274393b86b"
      ]
    }
  ]
}

Example response

{
  "success": true,
  "header": [
    {
      "type": "profile",
      "rows": [
        {
          "id": "564919357",
          "platform": "twitter",
        },
        {
          "id": "164929129743",
          "platform": "facebook"
        }
      ]
    },
    {
      "type": "metric",
      "rows": [
        "page_posts"
      ]
    }
  ],
  "data": [
    [
      3
    ],
    [
      6
    ]
  ]
}

 

Thanks 

 

Sonia

 

 

 

smarguin's avatar
smarguin
Advocate I
6 years ago

sorry... i realise that profile is not in the list of dimensions I request...