Displaying list of subitems in sqlline

Question

In Ignite I have classes defined as below (with cache configuration for Persons):

public class Person {
    @QuerySqlField(index = true)
    private long id;

    @QuerySqlField
    private String name;

    @QuerySqlField
    private List<Address> addresses;
}

public class Address {
    @QuerySqlField
    private String street;

    @QuerySqlField
    private int zip;
}

In sql line when selecting from person (select * from persons), I receive below exception: "Error: Statement is closed. (state=, code=0)"

I found that I can select all fields except of addresses and receive proper result. I thought that it was just unable to print lists. But than it appeared that similar setup had a working query for two other classes (Where the nested class had a separate cache). I tinkered with creating a cache for addresses adding or removing @QuerySqlField in nested classes, but I was not able to reproduce working behavior.

What are the requirements that needs to be made for sqlline queries neatly display array of nested objects?

Answer 1

Technically, nested collections are not supported in Apache Ignite SQL. Instead, you are expected to normalize your SQL schema while using JDBC, sqlline or other tools, i.e. to create a separate addresses table and to use joins. And the reason for that is - what would be your INSERT statement, and how would you provide addresses field?

On the other hand, since everything is just a K-V cache underneath, it's possible to utilize Java API for that task. For example, given your models definition but with all methods changed to public:

IgniteCache<Object, Object> cache = ignite.getOrCreateCache(new CacheConfiguration<>("myCache")
             .setIndexedTypes(Integer.class, Person.class)
             .setSqlSchema("PUBLIC"));

Person p = new Person();
p.id = 1;
p.name = "person";
p.addresses = new ArrayList<>();

cache.query(new SqlFieldsQuery("INSERT INTO PERSON (_KEY, id, name, addresses) VALUES(?, ?, ?, ?)").setArgs(0, 0, "hah", p.addresses));

Address addr = new Address();
addr.street = "street";
addr.zip = 123;
p.addresses.add(addr);

cache.put(1, p);
List<List<?>> all = cache.query(new SqlFieldsQuery("SELECT * FROM PERSON")).getAll();

for (Object next : all){
    System.out.println(next);
}

Output

[0, hah, []]
[1, person, [Address{street='street', zip=123}]]