Flink sql : how to find the size of json array

Question

I am using flink sql to read data from kafka. One field in kafka message is array, example

{
  "description": "som description",
  "owner": {
    "type": "some",
    "id": "5ff4eb4fed9b4b1288d7993944a8ca23"
  },
  "someArray": [
    {
      "type": "foo",
      "id": "c31a2d10134146e29726fb87246b68d0"
    },
    {
      "type": "foo1",
      "id": "c31a2d10134146e29726fb87246b68d0"
    }
  ]
}

i want to write a select statement similar to select description, size_of(someArray) from some_table;

Flink does NOT have size_of function. Can I get the length of someArray which is 2 in this example, using some built-in functions ?

I have tried to write an UDF for this, challenge i have with udf is when the query are executed with sql-gateway i get class not found exception on the UDF class ( its a java class ). When i try the same with cli sql-client i can get the udf to work. I have added the jar that contains the udf to /usr/local/Cellar/apache-flink/1.16.0/libexec/lib when running it on my machine

xjmdoo · Accepted Answer · 2023-03-03 09:34:47Z

1

You can use the built-in system function CARDINALITY to get the length of an array like so:

select cardinality(someArray) as array_length...;

For more information about collection functions in the table API, please check the docs.

answered Mar 3, 2023 at 9:34

xjmdoo

1,75611 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hitesh Over a year ago

I tried this select CARDINALITY(json_array('[1,2,3]')); select CARDINALITY('[1,2,3]') CARDINALITY does not table varchar, it needs multiset. I get this error

org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply 'CARDINALITY' to arguments of type 'CARDINALITY(<VARCHAR(2147483647)>)'. Supported form(s): 'CARDINALITY(<MULTISET>)'

Collectives™ on Stack Overflow

Flink sql : how to find the size of json array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related