CODEWITHSUNDEEP
sqlarrayssnappydata
Ashu
Ashu

Reputation: 357

How to retrieve stored array data in snappaydata?

I have sample array data in snappydata.

snappy-sql> select * from test;
ID         |DOMAINS                
-----------------------------------
1          |{"COL_0":[1,2]}        
2          |{"COL_0":[1,2,5,6,7,9]}

Inserted data using this query:

insert into test select 1, array(1,2);

insert into test select 2, array(1,2,5,6,7,9);

How to retrieve this stored array data? I tried this :

select * from test where DOMAINS having 1;

select * from test where DOMAINS = 1;

select * from test where DOMAINS IN 1;

select * from test where COL_0 HAVING 1;

Tried so many queries but these queries don't work for me.

Upvotes: 0

Views: 64

Answers (1)

jagsr
jagsr

Reputation: 535

If you are SQL from a remote client (i.e. JDBC or ODBC) you have to either flatten the projected result or use Serializer as shown in the example. But, if you can use the Spark API (Google search - working with complex data types in spark) you can navigate and access any of your nested structures in Spark SQL.

For remote SQL access(e.g. using the snappy shell) a simpler option would be too simply explode your result in the query like this ....

create table test using column as (select 1 as id, array(1,2) as nestedData from range(1))
insert into test select 2, array(1,2,3,4,5)
select id, explode(nesteddata) from test

+---+---+
| ID|col|
+---+---+
|  1|  1|
|  1|  2|
|  2|  1|
|  2|  2|
|  2|  3|
|  2|  4|
|  2|  5|
+---+---+

Upvotes: 1

Related Questions