How to get non-nested elements along with an array? Routine Load with Kafka

Working with Data Data Loading Tools & Integrations
1

Hello everyone!

StarRocks version 3.2.3

I’m trying to get non-nested JSON elements along with an array, but on the current solution i’m getting only non-nested elements OR an array elements.

How can i get them both?

My json:

{
  "code": "1",
  "main_code": "12",
  "merchant": {
    "uid": "123",
    "name": "test"
  },
  "datecreate": 1670967097491,
  "phone_number": [
    {
      "id": "1",
      "numbers": {
        "type": "iPhone",
        "number": "0000-1111-2222-3333"
      }
    },
	{
      "id": "1",
      "numbers": {
        "type": "Android",
        "number": "1111-2222-3333-4444"
      }
    }
  ]
}

Routine Load code:

CREATE ROUTINE LOAD test.phones_table_routine ON phones_table
COLUMNS(id, main_code, numbers_type, code, merchant_uid, merchant_name, datecreate, numbers_number)
PROPERTIES
(
    "format" = "json",
    "jsonpaths" = "[\"$.id\",\"$.main_code\",\"$.numbers.type\",\"$.code\",\"$.merchant.uid\",\"$.merchant.name\",\"$.datecreate\",\"$.numbers.number\"]",
    "json_root" = "$.phone_number",
    "strip_outer_array" = "true",
    "max_batch_interval"="15",
 	"desired_concurrent_number"="4"

)
FROM KAFKA
(
    "kafka_broker_list" = "broker",
    "kafka_topic" = "topic",
    "property.kafka_default_offsets"="OFFSET_BEGINNING",
    "property.security.protocol" = "SECURITY_PROTOCOL",
	"property.sasl.mechanism" = "SASL_MECHANISM", 
	"property.sasl.username" = "USER", 
	"property.sasl.password" = "PASSWORD",
	"property.group.id" = "GROUP_ID_1"
	    
);

With this code, currently, i’m getting only an array elements, but i want to add json-keys above than an array too.

How can i do this with StarRocks?

Thanks in advance.

2

Could you give us an example of what you want to return? Or what does the data look like after being ingested into starrocks?

3

Table result like below screenshot

image
image1080×83 5.16 KB

From this format

{
  "code": "1",
  "main_code": "12",
  "merchant": {
    "uid": "123",
    "name": "test"
  },
  "datecreate": 1670967097491,
  "phone_number": [
    {
      "id": "1",
      "numbers": {
        "type": "iPhone",
        "number": "0000-1111-2222-3333"
      }
    },
	{
      "id": "1",
      "numbers": {
        "type": "Android",
        "number": "1111-2222-3333-4444"
      }
    }
  ]
}