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 so that it returns the
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-14 1 2019-02-17 2019-02-21 1 2019-03-17 2019-03-21 Earlier I had asked this question for single-column split and below is the link How to replace special
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: This gives me the result: Classifier CORNXP21RTN How would I go about using the sale_date column in a where clause? For instance this shows me a list of the classifiers in
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 convert empty string values “”
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 year and
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_JSON_CONTENT] I
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 in the main query.) I am sorry,
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 JSON. How can I
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