Modelling a Json entitty with array of "details"

[dieman.one]

Dear Sirs,

Perhaps one more novice question. I am still trying to get by Spring Boot/REST data source example work.

The json return by my WS is this:

{

    "recordsFiltered": 10,

    "data": [

        [

            1,

            "<String1>\t253857",

            ""

        ],

        [

            2,

            "<String2>\t253859",

            ""

        ],

    ],

    "query": "<String3>",

    "hasMore": true,

    "time": 0,

    "draw": 0,

    "recordsTotal": 10

}

It's a kind of master table with embedded array of "data" objects, each containing exactly 3 values

Since there is no explicit join between "master" and "detail", I modeled it as one entitywith a collection property (Is this correct)?

My bean:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@JsonTable(endpoint="http://<...>", source="rest", root="/")

@Entity
public class ArthorSearchResult{

   @Id
  private String query;

  private Long time;
  private Long draw;
  private Long recordsFiltered;
  private Long recordsTotal;
  private Boolean hasMore;

private List<List<String>> data;

(getters and setter omitted/supplied by LOMBOK)
}

At start-up Spring throws this error:

Caused by: org.hibernate.MappingException: Could not determine type for: java.util.List, at table: arthor_search_result, for columns: [org.hibernate.mapping.Column(data)]

    at org.hibernate.mapping.SimpleValue.getType(SimpleValue.java:486)

Could you please point out what I am doing wrong?

Should I model the collection 'data' property:

1) as in my example - a Java collection?

2) As a collection of POJOs

3) As a collection of another entity comprising the 3 fields

Many thanks1

Dmitry

[dieman.one]

I am thinking that my trouble was caused by the fact that one of my bean properties was called 'time', which Teiid did not like very much (perhaps a reserved word?)

[rareddy]

I know there is support for List, but "List of Lists" I need to check to see if that support there, it needs to map to multi-dimensional array in Teiid.

[rareddy]

Yes, "time" is a reserved word in the SQL/DDL. What was the error? Maybe I can update the error message to be clear?

[dieman.one]

Hi,

The error message is:

Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "records_total long, [*]time[*] long )" at line 14, column 156.

Was expecting: id

    at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:249)

It does highlight *time* with asterisks, but meaning of the error is pretty cryptic.

Thanks for getting back on this. I was able to figure out that problem with time, but still struggling with modelling the Json structure with embedded array, like in my original post:

{

    "recordsFiltered": 18086,

    "data": [

        [1, "RESULT1", ""],

        [2, "RESULT2", ""],

    ],

    "query": "QUERY",

    "hasMore": false,

    "time": 553,

    "draw": 0,

    "recordsTotal": 18086

}

How should I model that "data" array inside my json? Variants I can think of:

• List<List<String>>
• Map<Integer, List<String>>
• POJO with one Inter, two Strings
• Another entity (JsonTable) plus many-to-one relation, (NOTE: there is an obvious problem with this approach - there is no foreign key to join on).

None of these seems to work. You mentioned a multi-dementional array, could you please provide exact syntax?

Would highly appreciate help.

Another thing I am struggling with passing parameters to my REST web service (I created a separate thread - (How to send the URI parameters to the REST web service via Teiid Spring Boot? )

Thanks,

D.

[shawkins]

Yes, time is a reserved word: BNF for SQL Grammar · GitBook

It appears that the sql generation logic in JsonTableView does not account name quoting.  Can you log an issue for that?

[dieman.one]

OK, could you please send me a link for this please? Never done this before.

[dieman.one]

As I reported on another thread, there is an issue creation issue.

SUMMARY: REST Web service integration: how to map nested structures?

DESCRIPTION:

Please describe how in Java Spring Boot Teiid one should model json with a nested structure. Example:

{

    "recordsFiltered": 18086,

    "data": [

        [1, "RESULT1", ""],

        [2, "RESULT2", ""],

    ],

    "query": "QUERY",

    "hasMore": false,

    "time": 553,

    "draw": 0,

    "recordsTotal": 18086

}

What Java class should be used to represent array "data" so that Teiid can correctly transform queries into REST web service calls.

[rareddy]

We do not support the mixed type arrays as in [integer, string, ..] they need to be a single simple type, for the mixed one like above you can try "List<Object>" but note any of SQL operations you can do on a specific data type you will lose that. for ex: using a function like `concat` etc.

[dieman.one]

Just tried out your recommendation: List<Object>. This causes the following error at application startup:

Caused by: org.hibernate.MappingException: Could not determine type for: java.util.List, at table: arthor_search_result, for columns: [org.hibernate.mapping.Column(data)]

    at org.hibernate.mapping.SimpleValue.getType(SimpleValue.java:486)

Just for the fun of I also tried out the following :

@ElementCollection(targetClass = String.class)

private List<Object> data;

Then application starts normally, but the any query I throw at that bean causes another error

2019-06-05 12:32:41 WARN  org.teiid.PROCESSOR - TEIID30020 Processing exception for request asKDfGQAeWrK.1 'Group does not exist: arthor_search_result_data AS data0_'. Originally QueryResolverException ResolverUtil.java:936.

org.teiid.api.exception.query.QueryResolverException: Group does not exist: arthor_search_result_data AS data0_

    at org.teiid.query.resolver.util.ResolverUtil.handleUnresolvedGroup(ResolverUtil.java:936)

which is perhaps expected - it can't cast an integer to a String..

So it looks like there is no way for me to connect to my web service as is? That web service is a part of a commercial product and changing returned json is not an optio unfortunately...

[rareddy]

I logged [TEIIDSB-105] Modelling a Json entitty with array of "details" - JBoss Issue Tracker  for this.

[rareddy]

It should be fixed on the master branch right now, will be available from 1.2.0 release.

[dieman.one]

Hi, thanks for that.

When do you think R 1.2.0 will be available?

[rareddy]

July 25th. 2019 is the target date. But you can download the master branch and compile and try out and let me know if you see any issues.