Function to insert rows of json array into a table: Call this function: or this form: or this form: Always received: Answer A JSON array (json) is different from a Postgres array of JSON values (json[]). vs: The first is an array nested inside a single JSON value, the second is an array of JSON values. Postgr…
Tag: arrays
Insert price to the pivot table
I am trying to insert data to a pivot table order_serrvice using arrays I follow this code: https://blog.quickadminpanel.com/master-detail-form-in-laravel-jquery-create-order-with-products/ the column qty and service was inserted properly but the price does not, I dont know why? order_service table: OrderCont…
Finding the intersection between two integer arrays in postgres
In postgres documentation I found that if I have two intarrays I can use the & operator to get the common elements between the two arrays(intersection), but this statement: raises this error: I have postgresql version 13 I am wondering why doesn’t this work, and how can I fix the query or try anothe…
Turn Cross Apply opensjson results into columns
We have a table that has a field with some json. using cross apply I was able to turn those pairs into columns: So how do I turn this into something like : Thanks. Answer If you know the limited set of possible key names in advance, you can just use PIVOT: But you can’t write a PIVOT if you
postgresql search column with integer[] with another array
I am trying to figure out a way that I can search a column containing an array of integers. For example, column a -> [1,2]. Using another array, ex: b -> [1,2,3] If all the values in a are in the array b, I want to return that result. I’ve tried using unnest and = ANY but to no avail.
Array to string with Django and MySQL
I need to design SQL query in html on Django I have an array in my table like : When I call this array in my page : I have : [‘aa’, ‘bb’, ‘cc’] What do I do if I want : a b c I tried with .split(‘,’) but I’ve already ‘[]’ I tried with for item …
How to achieve conditional array aggregate?
For simplicity I’ll use the following patient and appointment tables. What I want is one report that contains the following data. So what I need to do is: Get the patient names from patient table aggregate all appointment statuses of the patient aggregate all appointment times of appointments that have …
How to do the equivalent of ‘distinct’ on array field in BQ?
Let’s take the following data: It can also be generated in BQ with the following statement: How would I do the following two ‘distinct’ totals on the right of the following: That is, I want to get a “total” that doesn’t double-count, or rather, only gets the distinct items …
Hive Explode the Array of Struct key: value:
This is the below Hive Table And this is the data in the above table- Is there any way I can get the below output using HiveQL? I tried use explode() but I get result like that: Answer Use laterral view [outer] inline to get struct elements already etracted and use conditional aggregation to get values corres…
Create an array in Snowflake
I want to create an array constructed from two timestamps. After that, use this as a range to find the difference between the first element and the second element. The goal is to find the time difference between timestamps within business hours (9 am to 17 pm). What should be the right approach here? Can I cr…