I need to implement a logic in either Java OR SQL for the following. There are credit ratings from numbers 1 to 11. And corresponding to each rating say x, I have 3 grades, effectively :x+, x and x-. Difference between each grade is termed as a step. For example, 2 and 2- is single(1) step,2+ and 2- is double…
Tag: arrays
SQL Insert JSON into table column
while searching for a way to insert array into single db columns, I found an article about inserting JSON string. However it wasn’t explained how. I tried to search and find the way with no success. I have the following table: What i want to do is insert a Json arry in the address column. like: I though…
How can I split a string into character in Snowflake?
I need to split a string like “abc” into individual records, like “a”, “b”, “c”. This should be easy in Snowflake: SPLIT(str, delimiter) But if the delimiter is null, or an empty string I get the full str, and not characters as I expected. Answer Update: SQL UDF…
Update partial jsonb field in Postgres 13
can anyone help please optimize SQL request Postgres 13 (jsonb). Need to update the “percent” values inside the Jsonb field with the specified ID This example is working, but it works for a very long time on a large database. https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a521fee551f2cdf8b189ef…
How to query all rows where a given column matches at least all the values in a given array with PostgreSQL?
The request below: returns a list like this: How to get the ids for which id must have at least a and b, and more generally the content of a given array ? From the example above, I would get: Answer For two values, you can use windowing boolean aggregation: A more generic approach uses array aggregation:
Using left join to query and match JSON field in MySQL
Table A,field : pay_type_fk,type:json, Examples Data: [1,2,4] Table B,field : id type : bigint Examples Data: primary key SQL I use: Only pay can be found_ For the first record in the type table, I think we can query all of them with group_ CONCAT Answer If I follow you correctly, you can join on JSON_CONTAIN…
Mysql comma seperated to json-array
I have some fields in the database that are comma selerated, something like: a,b,c,d,e and I want to convert them into: I know how to do it in nodejs / any other language, but I need to do it directly on the mysql Possible? Thanks Answer The simplest approach probably is to use string functions only: Basicall…
find exact number in text field in sql
I am trying to query exact number in array inside text field in sql so for example can return which is not good because i would like to get the arrays that contain 1 Answer You could use JSON functions:
SQL – select where for specific id, all distinct values for other column exist
I am having a bit of a hard time explaining the logic I want to achieve in the title. Anyway, I have a database containing two tables (rent and car). The car table contains data about cars (license …
Check if an element is contained in the values (array) of a json column in MySql
I have the following values inside a cell of a json column in MySql: I would like to make a query that extracts data based on the existence of a value in the array at the fornitori key. For now I’ve tried this: Which print is: Basically the condition is that value (“8”) must be inside the …