Search code examples
javasqlignite

Ignite Cache SQL query with field Object[] array

I have a Ignite cache with the two classes below.

MyPerson class looks like this:

public class MyPerson implements Serializable
{
    @QuerySqlField()
    public String ID;

    @QuerySqlField()
    public Socials[] socials;
}

And the Socials class looks like this:

public class Socials implements Serializable
{
    @QuerySqlField()
    public String network;

    @QuerySqlField()
    public String login;
}

I create and populate the cache like this:

// creating person cache
CacheConfiguration<String, MyPerson> personCacheCfg = new CacheConfiguration<String, MyPerson>();
personCacheCfg.setName("Person");
personCacheCfg.setIndexedTypes(String.class, MyPerson.class);
IgniteCache<String, MyPerson> personCache = ignite.createCache(personCacheCfg);

// adding data
personCache.put("P1", new MyPerson("Person1", new Socials[] { new Socials("fb", "[email protected]"), new Socials("tw", "b.my.com") }));
personCache.put("P2", new MyPerson("Person2", new Socials[] { new Socials("tw", "c.my.com") }));
personCache.put("P3", new MyPerson("Person3", new Socials[] { new Socials("fb", "d.my.com") }));

Now, when I want to run a SqlFieldQuery that returns all Persons that have a specific Socials account, how would I do this with Ignite SQL? Would this be a join over the array field? Or is such case better done by storing the Socials in a separate cache?

Solution

  • You can't run SQL queries over nested collections or arrays. You should create an additional table of pairs (socials, personId) and run joins over these two tables.

    Make sure to collocate data of these tables by personId. Otherwise you will need distributed joins, that impact performance dramatically. You can find documentation on affinity collocation here: https://apacheignite.readme.io/docs/affinity-collocation

    Another option without use of SQL is a ScanQuery. But it requires a full data lookup. If you are going to select a big part of data anyway, scan query may be a good option too. Documentation: https://apacheignite.readme.io/docs/cache-queries#scan-queries