Questions tagged [lateral]
66 questions
213
votes
2 answers
Query for array elements inside JSON type
I'm trying to test out the json type in PostgreSQL 9.3.
I have a json column called data in a table called reports. The JSON looks something like this:
{
"objects": [
{"src":"foo.png"},
{"src":"bar.png"}
],
…
pacothelovetaco
- 2,361
- 3
- 16
- 12
30
votes
4 answers
Query for element of array in JSON column
Recently upgraded to using PostgreSQL 9.3.1 to leverage the JSONfunctionalities. In my table I have a json type column that has a structure like this:
{
"id": "123",
"name": "foo",
"emails":[
{
"id": "123",
"address":…
Joe
- 363
- 2
- 4
- 6
30
votes
4 answers
Postgres analogue to CROSS APPLY in SQL Server
I need to migrate SQL queries written for MS SQL Server 2005 to Postgres 9.1.
What is the best way to substitute for CROSS APPLY in this query?
SELECT *
FROM V_CitizenVersions
CROSS APPLY
dbo.GetCitizenRecModified(Citizen,…
user1178399
- 1,028
- 8
- 17
- 32
27
votes
1 answer
GROUP BY in Postgres - no equality for JSON data type?
I have the following data in a matches…
janderson
- 963
- 4
- 14
- 26
24
votes
2 answers
Flattening a relation with an array to emit one row per array entry
Given a table defined as such:
CREATE TABLE test_values(name TEXT, values INTEGER[]);
...and the following values:
| name | values |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |
I'm trying to find a query which will return:
| name…
Charles Duffy
- 280,126
- 43
- 390
- 441
10
votes
1 answer
JOIN on set returning function results
I am trying to join table and function which returns rows:
SELECT p.id, p.name, f.action, f.amount
FROM person p
JOIN calculate_payments(p.id) f(id, action, amount) ON (f.id = p.id);
This function returns 0, 1 or more rows for each id.
The query…
faskunji
- 151
- 1
- 2
- 7
8
votes
3 answers
Hive lateral view with sample example with hive table having 1 column as array
My use case is I am having one table in hive which has one column as INT and one as Array data type. I want to display it horizontally..
Nakul Dev
- 81
- 1
- 1
- 4
8
votes
3 answers
Using stored procedure returning SETOF record in LEFT OUTER JOIN
I'm trying to call a stored procedure passing parameters in a left outer join like this:
select i.name,sp.*
from items i
left join compute_prices(i.id,current_date) as sp(price numeric(15,2),
discount numeric(5,2), taxes numeric(5,2)) on…
franbenz
- 696
- 1
- 10
- 16
6
votes
1 answer
"invalid reference to FROM-clause entry for table" in Postgres query
I have the following query:
query =
"SELECT
data #>> '{id}' AS id,
data #>> '{name}' AS name,
data #>> '{curator}' AS curator,
data #> '{$isValid}' AS \"$isValid\",
data #> '{customer}' …
dipole_moment
- 5,266
- 4
- 39
- 55
5
votes
2 answers
Snowflake - Lateral cannot be on the left side of join
I have a variant data type that I am performing a lateral flatten on but I then need to left join one of the json elements to lookup the value for the corresponding ID from another relational table within Snowflake. When I do this it gives me the…
Tom Healy
- 51
- 1
- 2
5
votes
1 answer
Postgresql LATERAL vs INNER JOIN
JOIN
SELECT *
FROM a
INNER JOIN (
SELECT b.id, Count(*) AS Count
FROM b
GROUP BY b.id ) AS b ON b.id = a.id;
LATERAL
SELECT *
FROM a,
LATERAL (
SELECT Count(*) AS Count
FROM b
WHERE a.id = b.id ) AS b;
I understand that…
Yaroslav Malyk
- 409
- 5
- 15
5
votes
3 answers
Postgres - Lateral join with random values on both sides
I am trying to generate the following table:
random person a | random utility 1
random person a | random utility 2
random person a | random utility 3
random person b | random utility 4
random person b | random utility 5
random person b | random…
Dave Keele
- 51
- 1
5
votes
1 answer
Find most common elements in array with a group by
I have a table of rows with the following structure name TEXT, favorite_colors TEXT[], group_name INTEGER where each row has a list of everyone's favorite colors and the group that person belongs to. How can I GROUP BY group_name and return a list…
mhkeller
- 713
- 1
- 8
- 19
4
votes
1 answer
Upgrading to PostgreSQL 11: set-returning functions are not allowed in CASE
The following query stopped working when upgrading from PostgreSQL 9.6 to 11:
with doc as (select * from documents where name = doc_id)
select jsonb_array_elements_text(permissions)
from users
where users.name = user_name
union
select
case
…
stenci
- 8,290
- 14
- 64
- 104
3
votes
1 answer
Lateral Flatten Snowpipe data with mixture of arrays and dict
I have two different structured json files being piped in from a snowpipe. The only difference is that instead of a nested dict it has many nested arrays. I am trying to figure out how to transform structure 1 into one finalized table. I've…
Bigmoose70
- 453
- 6
- 15