Skip to content
Advertisement

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 contents without losing any data. Here

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 this “id” and result should be in column like this:

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’ Results: [ { “Categories”: [ { “QuerySourceNames”: [ “QAsset” ], “Id”:

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’}}, ‘securityObject’: {‘data’: None}, ‘segmentObject’: {‘data’: None}, ‘dataItemObject’: {‘data’: {‘type’: ‘dataItems’, ‘id’: ‘13161’}}, ‘scenarioObject’: {‘data’: {‘type’: ‘scenarios’, ‘id’: ’13’}}} Running: Gives me: id Invest_Type 1 null I would expect the column Invest_Type

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 OPENJSON and then extract the values of interest The same

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 permission on

Advertisement