Exploring BigQuery Audit Logs - identifying JSON metadata keys containing the '@' sign

When crafting queries in Log Explorer, we have the option to utilize the following syntax:

protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"

After successfully setting up a sink to store all logs into BigQuery, I'm attempting to extract the category using the following code snippet:

JSON_EXTRACT(PARSE_JSON(protopayload_auditlog.metadataJson), '$.@type')

Unfortunately, it throws an error stating 

Unsupported operator in JSONPath: @;
. How can I resolve this issue? Provided below is a sample payload:

{
  "@type": "type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata",
  "tableDataRead": {
    "fields": [
      "insertId",
      "logName",
      "protopayload_auditlog.metadataJson",
      "protopayload_auditlog.methodName",
      "receiveTimestamp",
      "resource.labels.dataset_id",
      "resource.labels.location",
      "resource.labels.project_id",
      "resource.type",
      "severity"
    ],
    "jobName": "projects/element-analytics-dev-poc/jobs/job_XvacfHSAAr_Og7zQFVNV9ioV9vfu",
    "reason": "JOB"
  }
}

I am inquiring about the method to query the JSON within BigQuery and retrieve the value of @type.

sqljsongoogle-bigquery

Answer №1

Try using the JSONPath expression "$['@type']" like in the example below

You can extract the value of the "@type" field from the given JSON data by running the following query:

WITH data AS (
  SELECT '''
{
  "@type": "type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata",
  "tableDataRead": {
    "fields": [
      "insertId",
      "logName",
      "protopayload_auditlog.metadataJson",
      "protopayload_auditlog.methodName",
      "receiveTimestamp",
      "resource.labels.dataset_id",
      "resource.labels.location",
      "resource.labels.project_id",
      "resource.type",
      "severity"
    ],
    "jobName": "projects/element-analytics-dev-poc/jobs/job_XvacfHSAAr_Og7zQFVNV9ioV9vfu",
    "reason": "JOB"
  }
}
  ''' AS json
)
SELECT JSON_EXTRACT_SCALAR(json, "$['@type']") AS type
FROM data

The above query will return the extracted value of the "@type" field as "type"

Here is an image showing the expected output:

https://i.stack.imgur.com/6vdTw.png

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Enhancing Firebase Data Manipulation with Swift: Exploring the updateChildValues Function's Capabilities in Overwriting

Encountering a unique challenge when attempting to update values at different JSON branches in Firebase simultaneously. The method outlined in the documentation works well for creating new data, let key = ref.child("posts").childByAutoId().key let post ...

jsonswiftfirebasefirebase-realtime-database

What are the best practices for incorporating CSRF tokens into Java applications to ensure security?

The Challenge: I encountered an issue with preventing CSRF attacks in my Java web application. To tackle this problem, I attempted to utilize the X-CSRF-Token implementation. Every time a request was sent, it looked something like this: POST /sessions HTT ...

javajsonsecuritycsrf-protection

The Battle Unveiled: Node MySQL versus JSON

I have been using MySQL to store JSON information and retrieve it for my application. However, I am considering removing MySQL from the equation. Is this a wise decision? Would it be efficient if I change my approach and store the data in a data folder as ...

mysqlnode.jsjson

Issues with displaying results from a local JSON file using Angular 4 services seem to be

I have developed a User.service.ts service where I have implemented the following code: getContactDetials(){ return this.http.get(this.config.apiUrl + 'assets/data/contact-details.json') .map(response => response.json()); ...

jsonangular

Is there a way to exclude a field during json.Marshal but not during json.Unmarshal in go language?

struct Alpha { Label text `json:"label"` ExcludeDuringSerialization string `json:"excludeDuringSerialization"` } func SerializeJSON(obj interface{}){ json.Serialize(obj) } How can I exclude the ExcludeDuringSerializa ...

jsongogo-gormgolang-migrategoogle-app-engine-golang

What is the best Java framework to use for developing an AJAX application?

After combing through various discussions on forums about basic Java web frameworks and Java web development, I realized that most do not touch upon the AJAX aspect. For the project I am currently working on, a significant portion of the client-side work w ...

javaajaxjsonframeworks

Modifying an object property by replacing it with a different value stored in the same object - JavaScript

Consider the object below: { id: 1, name: 'jdoe', currentDayHours: null, totalHours: [{ task: 'cleaning', hours: 10}, { task: 'reading', hours: 2 }]} A function is needed to update the currentDayHours based on the task param ...

javascriptjsonjavascript-objects

Is it possible to create a JSON array without specifying an array name?

@Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_map); Bundle b = getIntent().getExtras(); String Array = b.getString("ITEM_EXTRA"); String Tripname = ge ...

javaandroidarraysjson

Displaying JSON data on a Django template

As I worked on developing a website, I encountered an issue. The JSON data I am sending from views.py to my template is not displaying any content. data = { "philip": {"age": 20, "salary": 10000}, "jerry": {"age": 27, "salary": 30000} } names ...

pythonjsondjangopython-3.xdjango-templates

PHP API Integration for XBox

Check out this demo link: http://davidwalsh.name/xbox-api I decided to create a php file with the following content.. <?php // Customizations $gamertag = 'RyanFabbro'; $profileUrl = 'http://www.xboxleaders.com/api/profile/'.$gamert ...

phpjsonxbox

Is there a way to showcase multiple TableViews from a Json File using Swift?

I am looking to populate a TableView in Xcode (Swift) with information from a Json file. To illustrate, here is what I aim to achieve: My goal is to extract json data regarding countries, cities, and additional city details. On the initial screen, I want ...

iosjsonxcodeuitableviewswift3

The process of filtering JSON data in Angular can be easily achieved by utilizing the filter functionality

Seeking recommendations for useful books or online resources to learn how to filter JSON data effectively using angular filters. Interested in tackling more complex datasets. Any assistance on this matter would be greatly appreciated. ...

jsonangularjsfilter

Tips for transforming a DataFrame into a nested JSON format

I am currently in the process of exporting a dataFrame into a nested JSON format for D3.js. I found a helpful solution that works well for only one level (parent, children) Any assistance with this task would be greatly appreciated as I am new to Python. ...

pythonjsonpandasdataframed3.js

Utilizing handpicked information in Angular: A beginner's guide

Being new to Angular and programming in general, I am currently navigating through the intricacies of Angular and could use some guidance on utilizing selected data. For instance, I would like to use a personnel number view image here and send it to the b ...

jsonangulartypescriptfrontendbackend

retrieve data beyond a certain identification number

Below is the modified SQL query that requires some adjustments: SELECT DISTINCT ic.img_path, ic.id FROM images_community ic WHERE ic.delete_flag = 0 AND ic.status = 1 ORDER BY ( SELECT (count(id ...

phpmysqlsql

The power trio: jQuery, JSON, and PHP

Greetings, I'm inputting a name on a particular website and then submitting that name by clicking a button. This is the HTML Code: <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; ch ...

phpjqueryjson

Tips for parsing json data into a pandas dataframe from a compressed file

My zipped file is quite large, 1.5G in size, with 500 sub folders inside and 5000 json files under each sub folder. I am trying to read the json files into a python dataframe using the code snippet below, but I keep encountering an error. Can you provide ...

pythonjsonpandas

Reorganizing Array from PHP after Decoding Facebook Open Graph

I'm in the process of creating an app that would greatly benefit from suggesting a user's Facebook friends as they type. However, I've hit a roadblock when it comes to converting the Open Graph result (retrieved by accessing a user's fr ...

phparraysjsonfacebook-graph-api

Retrieving JSON database entries from MySQL using PHP

I'm currently working on an app with a challenging client who often changes their requirements. To handle this, I've decided to store some data as JSON objects. Within the app, I have a system for matching users and storing their information as ...

phpmysqljson

Having difficulty creating JSON data following retrieval of rows by alias in MySQL

I am encountering an issue while trying to fetch rows from two tables using a JOIN and aliases. The problem arises when I attempt to convert the fetched rows into JSON data and assign them to a JSON array. Below is the code snippet: $personal = $db->p ...

javascriptphpjquerymysqljson