Suppose we have two columns: id, params. params is a jsonb column that contains arrays of objects like: [ { "prop": "a", "answer": "123" }, { "prop": "b", "answer": "456" } ] I need to create a query that returns…
The rough layout I have for some complex queries follows this table structure in Postgres: CREATE TABLE primari ( id BIGINT NOT NULL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE secondary ( primary_id BIGINT NOT NULL references primari…
I have 2 tables that I join using LEFT JOIN, i.e. I take Name from table A and match it to Name from table B. There are some records that do not match and return null values due to it…
I have a device_events table in which I get signals from various devices. These events could be online offline events for a device. Basically I want all the offline and online events for a device to be paired. We can…
In PostgreSQL, the ON CONFLICT we are using can have multiple column names. Is that multiple columns behave like AND or OR? ON CONFLICT (name, mobile) DO UPDATE SET address = EXCLUDED.address; Does this mean, conflict occurs in name AND…
I'm trying to count multiple values in multiple coluns in a table and I did that: TABLE id risk record api 1 OK OK NO 2 OK OK OK 3 OK OK OK 4 OK NO OK 5 NO OK…
I am trying to create a nested json object using Oracle SQL. I am able to create JSON objects where the hierarchy level is predefined. In this case, it is dynamic and not able to find SQL or PLSQL solution…
First Attempt - CREATE FUNCTION rental_quarter (rental_date TIMESTAMP) RETURNS VARCHAR(7) BEGIN IF MONTH(rental_date) BETWEEN 1 AND 3 THEN RETURN YEAR(rental_date)+ ”Q1”; ELSE IF MONTH(rental_date) BETWEEN 4 AND 6 THEN RETURN YEAR(rental_date)+ ”Q2” ; ELSE IF MONTH(rental_date) BETWEEN 7 AND 9…
I am working with three tables in my MySQL database, and I need some help constructing a SELECT query. The structure of my tables is as follows: posts table: (postId, userId, post) likes table: (userId, postId) users table: (userId, email,…
Laravel Multiple Choices Input Error: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'crew_ids' for column uj.crew_uj.crew_id at row 1 insert into crew_uj (crew_id, uj_id) values (crew_ids, 15) UJController : public function store(Request $request) { // dd($request->all()); $validatedData = $request->validate([…