13 Replies Latest reply on Jun 14, 2019 6:49 AM by rareddy

    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

        • 1. Re: Modelling a Json entitty with array of "details"
          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?)

          1 of 1 people found this helpful
          • 2. Re: Modelling a Json entitty with array of "details"
            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.

            • 3. Re: Modelling a Json entitty with array of "details"
              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?

              1 of 1 people found this helpful
              • 4. Re: Modelling a Json entitty with array of "details"
                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.

                • 5. Re: Modelling a Json entitty with array of "details"
                  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?

                  • 6. Re: Modelling a Json entitty with array of "details"
                    dieman.one

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

                    • 7. Re: Modelling a Json entitty with array of "details"
                      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.

                      • 8. Re: Modelling a Json entitty with array of "details"
                        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.

                        • 9. Re: Modelling a Json entitty with array of "details"
                          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...

                          • 10. Re: Modelling a Json entitty with array of "details"
                            rareddy
                            • 11. Re: Modelling a Json entitty with array of "details"
                              rareddy

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

                              • 12. Re: Modelling a Json entitty with array of "details"
                                dieman.one

                                Hi, thanks for that.

                                When do you think R 1.2.0 will be available?

                                • 13. Re: Modelling a Json entitty with array of "details"
                                  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.