'select from array json (oracle)

I'm just learning to work with table. I have table:

id data
1 [[name_1, 2], [name_2, 1], [name_3, hello], [name_4, 5], [source, value_1], [size, Big]]
2 [[name_1, 6], [name_2, 4], [name_3, 99999saa4c], [size, Min], [name_4, 1], [name_5, text], [name_6, text]]

I want to get all the lines in which [size, Big]

Having read the documentation and choosing a simple way, I tried the following combination

select * from table where concat_ws(',', data) like '%Big%' OFFSET 1 ROWS

But i have error

Error while compiling statement: FAILED: SemanticException [Error 10016]: line 5:15 Argument type mismatch 'data': Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<struct<key:string,value:string>>" was found.

The question: Is there a way to convert column to text and make a search by it?

What optimal query, if I have 100 million lines and I need lines in which the meaning is contained [size, Big]



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source