question Re: Impala multiple Key values in where clause with complex type map in Support Questions

Impala multiple Key values in where clause with complex type map

Kjarzyna — Fri, 11 Oct 2024 12:04:04 GMT

I am having a table

id  | result
Int | map<string, string>

Id  | result
1   | {”street”:”abc”, ”phone”:”123”}
2   | {”country”:”def”, ”phone”:”456”}
3   | {”street”:”abc”, ”zipCode”:”726”}
4   | {”street”:”abc”, ”phone”:”123”}
5   | {”street”:”fgh”, ”phone”:”123”}

I would like to get records with street=abc and phone=123

I wrote the query like below:

select id, r
from table, table.result r
where r.key=’street’ and r.value=’abc’
and r.key=’phone’ and r.value=’123’

The result is empty

Re: Impala multiple Key values in where clause with complex type map

DianaTorres — Fri, 11 Oct 2024 15:43:47 GMT

@Kjarzyna Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Impala experts @jAnshula @Boris G who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Re: Impala multiple Key values in where clause with complex type map

Boris G — Sun, 13 Oct 2024 01:06:43 GMT

Hello-

Please see https://impala.apache.org/docs/build/html/topics/impala_complex_types.html#complex_sample_schema

for the correct schema definitions as well as examples of queries against complex data structures like ARRAY and MAP (which is implemented very similar to ARRAY in Impala).

Let me know if that helps.

Re: Impala multiple Key values in where clause with complex type map

Kjarzyna — Mon, 14 Oct 2024 06:24:37 GMT

Yes I saw the documentation, but i didn’t find solution there. In documentation you usually add just one map field and value into where clause

Re: Impala multiple Key values in where clause with complex type map

zegab — Tue, 15 Oct 2024 13:19:24 GMT

@Kjarzyna wrote:
Yes I saw the documentation, but i didn’t find solution there. In documentation you usually add just one map field and value into where clause

Hi @Kjarzyna ,

If you just add one single map key or value to the where clause, does your query work?

Re: Impala multiple Key values in where clause with complex type map

Kjarzyna — Tue, 15 Oct 2024 13:29:34 GMT

Yes, if I add just one Key and value into where clause query work correctly and return correct sets of data

Re: Impala multiple Key values in where clause with complex type map

zegab — Tue, 15 Oct 2024 14:32:52 GMT

In your where clause:

r.key=’street’ AND r.value=’abc’ AND r.key=’phone’ AND r.value=’123’

you are using the "and" operator between all the conditions. That would select a row/record where all of these conditions are true at the same time, but there are no such records. I think that's why you are getting empty results. You should use "OR" between conditions that applies to different rows, like:

(r.key=’street’ AND r.value=’abc’) OR (r.key=’phone’ AND r.value=’123’)

Re: Impala multiple Key values in where clause with complex type map

Kjarzyna — Wed, 16 Oct 2024 06:09:39 GMT

I wouldn’t like to get records with „or” operator, it will return more records than i really would like to take

Re: Impala multiple Key values in where clause with complex type map

Kjarzyna — Tue, 05 Nov 2024 09:43:24 GMT

@Boris G it doesn’t help

Re: Impala multiple Key values in where clause with complex type map

Meenambigai — Mon, 10 Mar 2025 13:20:00 GMT

Try query below and let us know if it solves the problem and any other issues you are facing related

select id, st.value street, ph.value phone
from table, table.result st, table.result ph
where st.key=’street’ and st.value='abc' and ph.key=’phone’ and ph.value='123'