Impala multiple Key values in where clause with complex type map

[#1]

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

[#2]

@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.

[#3]

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.

[#4]

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 

[#5]

---

@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?

[#6]

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

[#7]

@Boris G it doesn’t help

[#8]

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’)

[#9]

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