-2

I have a repeated value on Bigquery, example: {"a": 1, "b": 4, "c": 6, "d": 8}

From this, I want every possible combination of two. So the output I'd be looking for is {"aa", "ab", "ac", "ad", "ba", "bb", "bc", "bd", "da", "db", "dc", "dd"}. The labels used here are of size 1, but the real labels will be strings of length between 6 - 50.

For this particular use case, I do not actually care about the values of these labels. I will group by this label and do a count.

So the columns for my final output will look something like this:

date, first_value, second_value, total

2020-01-01, a, a, 1

2020-01-01, a, b, 1

...

How can I go about doing this?

T A
  • 1
  • 2

1 Answers1

0

try below

select t1.label as first_value, t2.label as second_value
from your_table t1
join your_table t2
on t1.label <= t2.label

if applied to sample/simple data in your question - output is

enter image description here

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Thank you for your response! The table data structure at the start will have multiple columns and the array itself will be a repeated value, so I'm not so sure this would work without using unnest? Example: date DATE, version STRING, markers REPEATED RECORD. I'll have to get from the above to the output that you have shown. Markers is the column of reference here. – T A Jan 22 '22 at 02:01
  • That is why I asked you already to present schema and example of real data! – Mikhail Berlyant Jan 22 '22 at 03:14