Skip to content

Tag: json

Repeating JSON Aggregated Array with Row Limits

I have an Oracle 19 database with a JSON aggregated array which I need to cut off and repeat after a set number of rows, for example: …etc. Here’s some basic SQL to illustrate how my current dataset is put together: I need to push customer data to an API endpoint. Pushing 1 customer record at a time is not

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

execute powershell from SSMS

I am trying to execute as certain xp_cmdshell code: but this is the error that I get: when I try an execute the same thing in powershell I get the expected response(see image) Powershell The expected output from the xp_cmdshell should be a json Answer The error message implies that your command line is being executed via cmd.exe, where &

How to insert an array value to an existing JSON document?

Given a column containing a JSON document, I can use JSON_SET() to set a key in the JSON document to a value. Supported values are null, true, false, numbers and strings; but I can’t figure out how to set an array. The example in the documentation (for JSON_INSERT(), but it works the same) weirdly shows how a naive user might

How to process json data and put it into arrays in postgresql

I have a postgresql table with two columns. name VARCHAR(255) and notes JSON. A sample dataset might look like this: Now I want to do two things. put the list from the json at ‘link_to’ into another column called referrals_to (which must be of array VARCHAR type then). From my example: name notes referrals_to ‘anna’ {‘link_to’: [‘bob’]} [‘bob’] ‘bob’ {‘link_to’:

Parsing JSON from SQL Server

I have a table with the following columns, one column being a JSON blob. I’m unclear how to parse the JSON blob as a series of columns alongside the other columns. I know there’s something called OPENJSON, but not sure how to apply it to this case. The json string example is below, from the DATA column above Answer openjson

How to Query JSON Within A Database

I would like to query information from databases that were created in this format: index label key data 1 sneaker UPC {“size”: “value”, “color”: “value”, “location”: “shelf2”} 2 location shelf2 {“height”: “value”, “row”: “value”, “column”: “value”} Where a large portion of the data is in one cell stored in a json array. To make matters a bit tricky, the attributes

Extract nested data in JSON variable in SQL

I need some help extracting the data I need out of a JSON variable in SQL: I need to get management_account_id, Month and cost_to_client/details in a table, the problem I am facing is that the month is higher up on the hierarchy than the details section, so I’m going into each individual month to access the details object, below is