How to parse the JSON returned by a query?

I just had a great conversation with one of our clients and realized that it would be helpful to have a guide on the structure of the data returned by our API. While each query will return data in a slightly different way, here’s a general guide for understanding response data.

Fact: It’s easier when you use a library :grin:

Before we dive in, I’d suggest everyone use a JSON parsing library to make the data returned more manageable. There are tons of libraries out there including JSON.NET, Python and JavaScript’s native json libraries, and Java’s Json API. Check out this page for a library in the language of your choice: www.json.org

Expressions to access objects

Here are some expressions you can use to access objects and fields returned by your query. You can c

data : Parent object that holds all the data in the query. This is returned for all successful queries.
data.items : List that contains objects that each represent an item.
data.items[0] : Access the first item in the list of items (Object)
data.items[0].name : The name of the first item (String)
data.items[0].id : The ID of the first item.
data.items[0].group : Object that represents the group that this item is in.
data.items[0].group.name : The name of the group of the first item (String).

Example data

Here is some data that corresponds to the expressions above:

{
  "data": {
        "items": [
          {
            "name": "Here's an item",
            "id": "161366694",
            "group": {
              "id": "new_group",
              "title": "This is a group"
            }
          },
          {
            "name": "And this one is also an item",
            "id": "161366701",
            "group": {
              "id": "new_group",
              "title": "This is a group"
            }
          },
          {
            "name": "Guess what this is? A item",
            "id": "161366747",
            "group": {
              "id": "new_group",
              "title": "This is a group"
            }
          },
          {
            "name": "A pulse can be a task",
            "id": "161366844",
            "group": {
              "id": "topics",
              "title": "Here's another group"
            }
          },
          {
            "name": "Or a project",
            "id": "160540651",
            "group": {
              "id": "topics",
              "title": "Here's another group"
            }
          }
        ]
      },
  "account_id": 1825528
}

Example query

Here is a query that will return the data above.

query {
  items {
    name
    id
    group {
      id
     title
    }
  }
}

Hello @dipro-monday.com,
This is an interesting thread as it relates to a specific need I have.
How should I modify this query to specify the board from which I want the items to come from?
I am currently using something like this
query {boards (ids: 246352052, limit:100) { items { id name column_values { text title value } created_at } } }

The issue I have, is that it places all the items under the data.boards object, and this creates issues with BigQuery when importing the result to a table.
Do you see any alternative in which I could get something like data.items[0] directly? (so, items positioned one level up the hierarchy)

Hey @PabloVerano – at the moment no, you’ll need to access them one level down via the data.boards.items object.

Does BigQuery allow you to reshape the data before it ingests it? I’m thinking of something like the excellent (and magical) jq utility.