Forum Discussion

bboysen's avatar
bboysen
Regular Visitor
3 years ago
Solved

JSON Keys Change for each record

Hello,

 

I have a JSON file that I am pulling via API; I am running into an issue when trying to expand the record into individual columns to access the values. Below is a snip of the JSON object.  I am trying to access the start and stop within the time_bucket, but the next JSON key changes each time that value gets updated. Any insight on how to dynamically access the data would be greatly appreciated.

 

"-NNNhF0luJcfv-QqKwux": {
            "sections": {
                "midpoint_section": {
                          "time_bucket": {
                            "-NNgtF6n9q3qeaE_ArL6": {
                                "start": 1675790384409,
                                "stop": 1675790451218,
                                "uid": "CcjuXG7To7XXP0C0R696v7n1zHk2"
                              }
                        }
                    }
        },
 "-NNNhFuUfWs6cL9u3Y_S": {    
            "sections": {
                "midpoint_section": {
                        "time_bucket": {
                            "-NNgtyoJmdB5xbNb92fX": {
                                "start": 1675790606877,
                                "stop": 1675790642483,
                                "uid": "CcjuXG7To7XXP0C0R696v7n1zHk2"
                            }
                        }
                    }
        }
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi bboysen,

     

    the code which I posted works with a record and I think that you are trying to apply it to a table (according to what I see).

     

    try using AddColumn [from the point on your screenshot, i.e. the time_stamp column does exist, but not yet expanded] to add this code:

    Record.Field([time_bucket], Record.FieldNames([time_bucket]){0})

    This will/should estract the record with start/stop regardless of the actual "tag" of the record.

     

    Cheers,

    John  

    I think 

4 Replies

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi bboysen,

     

    This is a generalised approach, let me know if you'll have difficulties adopting it for your scenario:

    let
    Source = "{""-NNNhF0luJcfv-QqKwux"": {#(cr)#(lf)            ""sections"": {#(cr)#(lf)                ""midpoint_section"": {#(cr)#(lf)                          ""time_bucket"": {#(cr)#(lf)                            ""-NNgtF6n9q3qeaE_ArL6"": {#(cr)#(lf)                                ""start"": 1675790384409,#(cr)#(lf)                                ""stop"": 1675790451218,#(cr)#(lf)                                ""uid"": ""CcjuXG7To7XXP0C0R696v7n1zHk2""#(cr)#(lf)                              }#(cr)#(lf)                        }#(cr)#(lf)                    }#(cr)#(lf)        }#(cr)#(lf)}#(cr)#(lf)}",
    json = Json.Document(Source),
    record = List.Last(List.Generate(()=>json, each not List.Contains(Record.FieldNames(_), "start"), each Record.Field(_, Record.FieldNames(_){0}))),
    Custom1 = Record.Field(record, Record.FieldNames(record){0})
in
    Custom1

     

    Kind regards,

    John

    • bboysen's avatar
      bboysen
      Regular Visitor
      3 years ago

      John, thank you for the information this works great with the one recorded. When I apply this solution to my main data file it gets the following error message. 

       

       

       

      Little more detail and I should have included some of this before. When I click to expand time_bucket I get the following 

      When I click to load more, I get about 200 more records within the list. 

       

      Each one creates a new column within the table. 

       

      I am trying to figure out how to get down to the next record without creating new columns for each of the parent records. 

       

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        Hi bboysen,

         

        the code which I posted works with a record and I think that you are trying to apply it to a table (according to what I see).

         

        try using AddColumn [from the point on your screenshot, i.e. the time_stamp column does exist, but not yet expanded] to add this code:

        Record.Field([time_bucket], Record.FieldNames([time_bucket]){0})

        This will/should estract the record with start/stop regardless of the actual "tag" of the record.

         

        Cheers,

        John  

        I think