Retrieving Data for Specific Key in JSON Object using BigQuery

I am facing a challenge with a string object stored in a table column, having the following structure:

[{"__food": "true"},{"item": "1"},{"toppings": "true"},{"__discount_amount": "4.95"},{"__original_price": "24.95"}]

Is there a way to retrieve the value true from the toppings key within this structure?

My attempt was to convert it into JSON first, however, 

json_extract(parse_json(string_object_column), '$.toppings')
only returns null.

The closest result I achieved was by keeping it as a string and executing 

json_extract(string_object_column, '$[0]')

This gave me: 

{"toppings":"true"}

Can this be accomplished without unnesting?

sqljsongoogle-bigquery

Answer №1

One possible solution to consider involves utilizing the REGEXP_EXTRACT function:

SELECT REGEXP_EXTRACT('[{"__food": "true"},{"item": "1"},{"toppings": "true"},{"__discount_amount": "4.95"},{"__original_price": "24.95"}]', r'"toppings": "(\D+)"}') as EXTRACT_TOPPINGS

OUTPUT:

https://i.stack.imgur.com/2t5c6.png

To tailor this regex pattern more accurately for your specific scenario, feel free to adjust it accordingly.

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

Error encountered while configuring the log4js function in a NodeJs environment

Encountering an issue with my NodeJs application where I'm using log4js for logging. The error in the console window is related to a syntax problem while running in the express runtime platform. Error: undefined:1 ?{ ^ SyntaxError: Unexpected token ...

jsonnode.jsfilelogginglog4js-node

Make sure to review for any duplicate entries prior to making updates to

Here is the issue I am facing... I am working with an SQL table that contains detailed information about flights, such as arrival date, arrival time, departure date, departure time, and more. When updating a flight entry, I need to ensure that the dates o ...

phpjquerymysqlsql

What is the reason behind the `inputs` function skipping the initial line of the input file?

Would you like to know why I only receive back all lines except the first when using jq with the `inputs` command and providing a file? My current version of jq is 1.6, and my goal is to convert a TSV (Tab Separated Values) into JSON by setting the first ...

jsoncsvjq

By setting the content_type to 'application/json' in Django HttpResponse, the response will render JSON data

Attempting to implement an ajax call using jquery to a Django view. The view interacts with an external API and then sends back the result. Trying to receive the data and handle it within the success callback of the ajax call, but instead receiving a rende ...

ajaxjsondjango

Struggling with retrieving information from a local JSON file using React

I am encountering an issue in my React project while trying to access a local JSON file located in the src folder using fetch. [ { "id": 1, "field1": "some data", "field2": 63, "field3": ...

reactjsjsonfetch

Removing all repetitions from an array in JavaScript

My collection of objects includes the following inputs: var jsonArray1 = [{id:'1',name:'John'},{id:'2',name:'Smith'},{id:'3',name:'Adam'},{id:'1',name:'John'}] There is a dupl ...

javascriptarraysjsonduplicates

Unraveling JSON in PHP

Given the JSON string above, I am looking to extract only the email address using PHP. How can this be achieved? {"communications":{"communication":[{"@array":"true","@id":"23101384","@uri":"xyz/v1/Communications/1111","household":{"@id":"111111","@uri" ...

phpjson

I am experiencing an issue with AngularJS where it is not retrieving JSON data

I have been working on a custom service and trying to get it to return the desired JSON data, but I am having trouble getting any results. I've tried everything I can think of, but nothing seems to be working. Does anyone have any idea what I might be ...

jsonangularjsservice

Show data in Tabular Format

How can I display all Full name values in a TABLEVIEW from the provided JSON data? Do I need to convert it to an Array or can I use a Dictionary directly? Below is a sample of how the JSON data looks: "Beneficiaries": [ { "BeneficiaryType": ...

iosobjective-cjson

Update the displayed image on the webpage based on information retrieved from the database

Can someone help me figure out how to change the clickable icon on getseats.php from available to unavailable when a seat's status is 0? I'm struggling with this and any advice would be appreciated. Here's the code I have: <?php $noerro ...

javascriptphpajaxjson

Perform an UPDATE query using the results of a SELECT query all in one single statement

Hello, I am facing a challenge with 2 SQL statements. First Statement: SELECT id, name, version FROM mydb WHERE device IS NULL AND Activated=1 ORDER BY id ASC LIMIT 10 Second Statement: UPDATE mydb SET device='$device' WHERE name IN ('$i ...

phpmysqlsql

Is there a way to specifically use nl2br() for just one row when sending everything to an array?

In our database, there are a total of 20 rows. One of the rows is structured as follows: 1) cool text 2) not really 3) something else? The rest of the rows contain one line of data each. When it comes to outputting a single row with line breaks, we ut ...

phpjson

JSON encoding function not invoked as expected

I attempted to utilize the martineau solution in a related case, but for some reason unknown to me, the custom encoder is not being invoked by the json.dump() method. from collections.abc import MutableMapping import json import numpy as np class JSONSeri ...

pythonjson

Traveler encountering Flask website displaying 500 error on Dreamhost platform

I'm struggling to configure Passenger with Flask on my DreamHost server. The Flask site is running inside a virtualenv with Python 3.5.2. The goal is to take input from a query string in the URL, parse a JSON file on the server, search for the given q ...

jsonpython-3.xflaskpassengerdreamhost

Self-reference within a JavaScript object involves creating a property that points

Can you reference another part of a JSON object within the same JSON object? In the code snippet below, there is an object that refers to the "home" object within the "MapParameters" object. { "parameters": { "data": { "URL": "http://SC.json ...

javascriptjson

Displaying data on the user interface in Angular by populating it with information from the form inputs

I am working on a project where I need to display data on the screen based on user input received via radio buttons, and apply specific conditions. Additionally, I require assistance in retrieving the id of an object when the name attribute is chosen from ...

javascripthtmljsonangulartypescript

Unable to Interpret JSON in Universal Windows Platform

I developed a UWP application that parses data to JSON format. The JSON data can be found here. However, I am facing issues with parsing the "jawaban" section of the JSON data which is causing an error message to appear like this: https://i.stack.imgur.co ...

c#jsonuwp

Tips on effectively deserializing JSON elements with identical element collections in C#

I am facing a challenge with deserializing a collection of elements in JSON that contains nested collections of the same elements in C#. How can I achieve this? So far, I have attempted to solve this by creating a C# class for the elements and defining pr ...

c#.netjson

Angular view showcasing a JSON array

After retrieving data from the Laravel API, I used the following method: this.dataService.getData().subscribe(res=>{ this.contacts=res }); Upon receiving a JSON array response from Laravel like the one below, I attempted to iterate throu ...

jsonangularlaravel

Problem: Values are not being posted with AJAX when using $(form).serialize()

I'm encountering an issue with submitting a form using AJAX. I initially tried to pass the data using $("#myForm").serialize(), but for some reason, the receiving page doesn't receive the data. Take a look at my form: <form id="myForm"> ...

javascriptphpjqueryjsonajax