I want to detect duplicate nodes from JSON before insert them in SQL Server table. I have this stored procedure that takes a JSON parameter like this: I want to return exception if @SalaryItems has duplicate key How can I detect duplicate in this table in my code? Answer Use OPENJSON() without a with clause s…
Tag: json
Oracle 12c Json split
This is how I am getting result in Oracle 12c Id Start Date Range End Date Range 1 [ “2019-01-07″,”2019-02-17″,”2019-03-17”] [ “2019-01-14″,”2019-02-21″,”2019-03-21”] And I want it Id Start Date Range End Date Range 1 2019-01-07 2019-01-1…
How to use where clause referencing a column when querying a JSON object in another column in SQL
I have the following sales table with a nested JSON object: sale_id sale_date identities 41acdd9c-2e86-4e84-9064-28a98aadf834 2017-05-13 {“SaleIdentifiers”: [{“type”: “ROM”, “Classifier”: “CORNXP21RTN”}]} To query the Classifier I do the following: T…
Put a json inside another json on Oracle 19
i have the next problem. I need create a json from a table that contain another json inside in one of the columns Having this: i try with this query: But the result is not correct: Must be something like: There is a way to get the correct result? Regards. Answer after see examples and see the resuslts. I know
Get rid of all empty strings values in jsonb | Postgres
I have rather abstract question on PostgreSQL jsonb data. For example I have a table called… table_one, where I have a column: In 100% cases in contains flat json structure like example: might be different length or null but always flat. My goal is like that. Whenever I select this column I need to somehow co…
Store hourly data efficient way
There is a requirement to store hourly data in SQL Server 2016 and retrieve. It’s an OLTP database. I will explain with an example: we need to capture temperature of each city of a country and store on hourly basis. What would be the best and efficient design to do this. The data would be stored for a y…
GET last element of array in json column of my Transact SQL table
Thanks for helping. I have my table CONVERSATIONS structured in columns like this : [ ID , JSON_CONTENT ] In the column ID i have a simple id in Varchar In the column JSON_CONTENT i something like this : I would like to query and get the id and the last element of exchanges : [ ID , LAST_ELT_IN_EXCHANGE_IN_JS…
Count values between double quotes and brackets
How can I group by and count the values separated by double quotes between the brackets? I have 400K rows, so I’m also concerned about performance. Desired output: Answer Do you mean something like this? (The with clause is only for testing – remove it, and use your actual table and column names i…
Transform JSON rows into PostgreSQL Table
I have a table in my database that returns me some columns. One of them is a list of JSONs that are, in fact, other tables, but in JSON format. The image belows show an example. I want to transform each row of that column “data” into separate tables, because I need specific information inside each…
How to extract values from array json column into multiple rows in Postgresql?
How can I extract values from the json arrays in ranges column as multiple rows Postgresq? Expected result: Start End 1 100 101 1000 1001 2000 2001 2002 Answer You can use jsonb_to_recordset function for this : http://www.sqlfiddle.com/#!17/22d62/10