Given the scenario that I would like to store sales of a store in a table in a database in Google Cloud Spanner. That table would (in addition to the sale ID as the primary key) have to store a list of product IDs sold, along with the quantity of each product sold. Ideally, this would be in the form
Tag: json
Return all values from objects using OPENJSON()
Here is my problem: My JSON may vary based on API response and there is no guarantee that “shortTitle”, for example, can be present in my JSON next time when there is a post request. I have to figure how to “loop” (if that’s the correct term) through JSON to get all fieldID and c…
Using JSON_VALUE for parse column in SQL Server table
I have never worked with JSON in SQL Server before that’s why need some help. I have written a simple snippet of code: In sample above I write code how get first “id” from “tags”. But how looks like script if in “tags” not 2 “id”, but an unknown number thi…
Is there an UPDATE equivalent command to SELECT json data
Is there a way to update the data retrieved from the below select (in this case, change “MS220” to something else)? It’s difficult enough to do select from JSON in some cases. I’m not sure how to update just a single element. Answer Try JSON_MODIFY() with the path ‘$[0].Name̵…
How to get data from json column in SQL Server that starts with array element
I have a database table that I need to extract data from where the column of interest has json it. What makes this particularly difficult is the most outer elements of the json is ‘[‘ & ‘]’ as in the parent element is an array. I need to get the value associated with key ‘Nam…
BigQuery JSON – Scalar subquery produced more than one element
I am trying to parse this json in BQ. My desired output is variant helm,chart I have tried the query below and I get the error: Scalar subquery produced more than one element.. If I rewrite this to use a nested arrays, everything works perfect. however, when I try to use a scalar subquery by following the sam…
Why is json_extract_scalar returning null
I have a fairly simple string-formatted json column in a BigQuery database I am trying to flatten. id relationships 1 {‘ownerObject’: {‘data’: None}, ‘investmentObject’: {‘data’: {‘type’: ‘assets’, ‘id’: ’40’}}, &#…
Create a JSON object to be sent as parameter to Stored Procedure
I have a stored proc say sp_proc_1 which takes in a few parameters say @val1, @val2, and @val3. I would like to make a JSON object using the values @val1 and @val2 so that I can pass that JSON object as one of the parameters to a second stored procedure sp_proc2 which I will be invoking from sp_proc1. Please …
JSON column parse in SQL
one of the columns in my database contains an imported JSON file. Format of JSON: There are hundreds of objects with taskId (3271012,3261201,…) how can i parse this tasks from one column to multiple rows? expected output: Answer First you can get all the “data” items as key-value pairs with …
PostgreSQL verify an empty array on json
I have the following row on select I have to Identify empty jsons, then manually add a value to it (eg row 1 and 3 ), I tried the following : But the “is null” verification fails for this type of data (array of json), how to identify ‘[]’ values in this case? Note: I only have select p…