Query JSON Fields in Big Query by Custom Function

Query Any Kind of Data in Big Query’s JSON Field even if you don’t know the keys name exactly or you are using dynamic keys so in this way you can do it.

Step 1:

View the format / schema of JSON field, simply by SELECT Clause.

SELECT json_field FROM `mytable`

Sample Output:

{
  "json_field": {
    "user1": {
      "likes": [
        {
          "emoji": "👍",
          "comment": "nice guy"
        },
        {
          "emoji": "❤️",
          "comment": ""
        }
      ]
    },
    "user9": {
      "likes": [
        {
          "emoji": "😎",
          "comment": "ego"
        },
        {
          "emoji": "❤️",
          "comment": "engaging"
        }
      ]
    },
    "user45": {
      "likes": [
        {
          "emoji": "👍",
          "comment": "good"
        },
        {
          "emoji": "👌",
          "comment": ""
        }
      ]
    }
  }
}

In the above example we have a JSON field which stores the data of likes received by the user. And they are nested under the user id of providing user and for every user this data is different so we need a way that can extract these likes and user ids. And then we can perform join to get information about the providing users.

Step 2:

Now i am writing the logic in JavaScript for extracting the information from the JSON field.

const results = [];
try {
  const likesData = JSON.parse(data); // if json_field is stored as string

  for (const userId in likesData) {
    const user = likesData[userId];
    if (user?.likes) {
      for (const like of user.likes) {
        results.push({
          status: 'received',
          emaoji: like?.emoji,
          comment: like?.comment,
          givenBy: userId,
        });
      }
    }
  }
}catch(e){}

Now after getting my required format i can use this code inside my SQL Query to get the result, Let me show you how:

    CREATE TEMP FUNCTION flatten_likes(data STRING)
    RETURNS ARRAY<STRUCT<status STRING, emoji STRING, comment STRING, givenBy STRINGtKey STRING>>
    LANGUAGE js AS """
      const results = [];
      try {
        const likesData = JSON.parse(data);

        for (const userId in likesData) {
          const user = likesData[userId];
          if (user?.likes) {
            for (const like of user.likes) {
              results.push({
                status: 'received',
                emaoji: like?.emoji,
                comment: like?.comment,
                givenBy: userId,
              });
            }
          }
        }
      }catch(e){}
      return results;
    """;

    SELECT flatten_likes(json_field) AS likes
    FROM `mytable`;

The above Query will create a temporary function named flatten_likes(). And this function is used inside the query to extract the likes attribute value. The Sample output will be:

{
  "likes": [
    {
      "status": "received",
      "emoji": "👍",
      "comment": "nice guy",
      "givenBy": "user1"
    },
    {
      "status": "received",
      "emoji": "❤️",
      "comment": "",
      "givenBy": "user1"
    },
    {
      "status": "received",
      "emoji": "😎",
      "comment": "ego",
      "givenBy": "user9"
    },
    {
      "status": "received",
      "emoji": "❤️",
      "comment": "engaging",
      "givenBy": "user9"
    },
    {
      "status": "received",
      "emoji": "👍",
      "comment": "good",
      "givenBy": "user45"
    },
    {
      "status": "received",
      "emoji": "👌",
      "comment": "",
      "givenBy": "user45"
    }
  ]
}

Now i can use this likes array to perform join and get the more details of providing users like their name. So we will have a view as follows:

user name status emoji comment given_by provider_name

Step 3:

Finally joining this json_field with same table as it has the Recusrive Relationship. So the final query will be:

CREATE TEMP FUNCTION flatten_likes(data STRING)
    RETURNS ARRAY<STRUCT<status STRING, emoji STRING, comment STRING, givenBy STRINGtKey STRING>>
    LANGUAGE js AS """
      const results = [];
      try {
        const likesData = JSON.parse(data);

        for (const userId in likesData) {
          const user = likesData[userId];
          if (user?.likes) {
            for (const like of user.likes) {
              results.push({
                status: 'received',
                emaoji: like?.emoji,
                comment: like?.comment,
                givenBy: userId,
              });
            }
          }
        }
      }catch(e){}
      return results;
    """;

    SELECT u1.userId AS user, u1.name, l.status, l.emoji,
      l.comment, l.givenBy AS given_by, u2.name AS provider_name
    FROM `mytable` AS u1
    JOIN UNNEST(flatten_likes(json_field)) AS l
      ON l.givenBy != u1.userId
    JOIN `mytable` AS u2
      ON l.givenBy = u2.userId;

Sample Output:

[
  {
    "user": "user5",
    "name": "ravi",
    "status": "received",
    "emoji": "👍",
    "comment": "nice guy",
    "given_by": "user1",
    "provider_name": "sumit"
  },
  {
    "user": "user5",
    "name": "ravi",
    "status": "received",
    "emoji": "❤️",
    "comment": "",
    "givenBy": "user1",
    "provider_name": "sumit"
  },
  {
    "user": "user5",
    "name": "ravi",
    "status": "received",
    "emoji": "😎",
    "comment": "ego",
    "givenBy": "user9",
    "provider_name": "pankaj"
  },
  {
    "user": "user5",
    "name": "ravi",
    "status": "received",
    "emoji": "❤️",
    "comment": "engaging",
    "givenBy": "user9",
    "provider_name": "pankaj"
  },
  {
    "user": "user5",
    "name": "ravi",
    "status": "received",
    "emoji": "👍",
    "comment": "good",
    "givenBy": "user45",
    "provider_name": "keshav"
  },
  {
    "user": "user5",
    "name": "ravi",
    "status": "received",
    "emoji": "👌",
    "comment": "",
    "givenBy": "user45",
    "provider_name": "keshav"
  }
]

I Hope this article will help you. Let me know if you need any further clarification.