CODEWITHSUNDEEP
apache-kafkaconfluent-platformksqldb
Segmented
Segmented

Reputation: 2044

Inserting arrays with nested structure in KSQLDB

I am trying to insert nested array objects to KSQL table. My table structure is as follows:

CREATE TABLE nlpArticlesTrain ("articleText" VARCHAR,
  "ner" ARRAY<STRUCT<"text" VARCHAR, "label" VARCHAR>>,
  "rel" ARRAY<STRUCT<"head" VARCHAR, "tail" VARCHAR, "rel" VARCHAR, "prob" DOUBLE>>)
  WITH (KAFKA_TOPIC = 'nlpArticlesTrain', PARTITIONS=1, REPLICAS=1, VALUE_FORMAT='AVRO');

I know I can push using standard Kafka producer with Avro support but I am looking for a way to INSER INTO VALUES to the table so that the stream/topic underneath is populated. From the documentation of query with structured data I am missing an example for insert.

INSERT INTO nlpArticlesTrain (articleText,ner,rel) VALUES ("string", [{..}],[{..}]) does not work.

A more concrete example:

INSERT INTO nlpArticlesTrain (articleText,ner,rel) VALUES ("some", [{'text': 'The Mexican Ministry of Health', 'label': 'ORG'}, {'text': 'Tuesday', 'label': 'DATE'}, {'text': 'at least 29', 'label': 'CARDINAL'}], [{'head': 'The Mexican Ministry of Health', 'tail': 'Tuesday', 'rel': 'subsidiary', 'prob': 0.3873162269592285}])

Upvotes: 0

Views: 2589

Answers (1)

Andrew Coates
Andrew Coates

Reputation: 1893

It depends what version of ksqlDB you're on. Later versions support the ARRAY and STRUCT constructors, which is what you need. For example:

CREATE STREAM TEST (K STRING KEY, A ARRAY<STRUCT<FOO INT>>) 
    WITH (kafka_topic='test_topic', value_format='JSON');

INSERT INTO TEST (A) VALUES (ARRAY[
   STRUCT(FOO := 1),
   STRUCT(FOO := 2)
]);

Upvotes: 3

Related Questions