CODEWITHSUNDEEP
sqlarraysjsonclickhouse
LaGabriella
LaGabriella

Reputation: 51

How to access a specific value with two separate Array with SQL (one with name and the other one with the values)

I have two columns as below:

names: Array(String)
['name_one','name_2','name3']

values:Array(Float64)
[1000,2000,3000]

For example, I am interested in getting the value of 'name_2'. I want to retrieve 2000. My guess is that I should first identify the location of 'name_2' in names, and then use it to retrieve the value in column values? Would you use JSON to get to the solution ?

PS. I have just started to learn SQL, I am only familiar with basics at the moment. I have read some documentation but I am quite struggling on that one (getting errors always)

I am using Clickhouse.

Thanks for the help !

Upvotes: 0

Views: 770

Answers (2)

Denny Crane
Denny Crane

Reputation: 13360

If you need to extract name multiple occurrences

SELECT arrayFilter((x, y) -> (y = 'name_2'), values, names)
FROM
(
    SELECT
        1 AS id,
        ['name_one', 'name_2', 'name3', 'name_2'] AS names,
        [1000, 2000, 3000, 4000] AS values
)

┌─arrayFilter(lambda(tuple(x, y), equals(y, 'name_2')), values, names)─┐
│ [2000,4000]                                                          │
└──────────────────────────────────────────────────────────────────────┘

if single

SELECT values[indexOf(names, 'name_2')]
FROM
(
    SELECT
        1 AS id,
        ['name_one', 'name_2', 'name3'] AS names,
        [1000, 2000, 3000] AS values
)

┌─arrayElement(values, indexOf(names, 'name_2'))─┐
│                                           2000 │
└────────────────────────────────────────────────┘

Upvotes: 1

vladimir
vladimir

Reputation: 15226

Consider using arrayZip-function:

SELECT
    arrayZip(names, values) AS zipped,
    zipped[2] AS second_pair,
    second_pair.1 AS second_name,
    second_pair.2 AS second_value
FROM 
(
    SELECT
        ['name_one', 'name_2', 'name3'] AS names,
        [1000, 2000, 3000] AS values
)

/*
┌─zipped─────────────────────────────────────────────┬─second_pair─────┬─second_name─┬─second_value─┐
│ [('name_one',1000),('name_2',2000),('name3',3000)] │ ('name_2',2000) │ name_2      │         2000 │
└────────────────────────────────────────────────────┴─────────────────┴─────────────┴──────────────┘
*/

Probably ARRAY JOIN-clause can be useful too:

SELECT *
FROM 
(
    SELECT
        1 AS id,
        ['name_one', 'name_2', 'name3'] AS names,
        [1000, 2000, 3000] AS values
)
ARRAY JOIN
    names,
    values

/*
┌─id─┬─names────┬─values─┐
│  1 │ name_one │   1000 │
│  1 │ name_2   │   2000 │
│  1 │ name3    │   3000 │
└────┴──────────┴────────┘
*/

Look at Nested Data Structures to store paired values.

Upvotes: 0

Related Questions