Can't access the data from the left join table

hi. have ksql code

CREATE STREAM IF NOT EXISTS input (data VARCHAR, location STRUCT<longitude VARCHAR, latitude VARCHAR>, keywords VARCHAR) WITH (KAFKA_TOPIC='sensors', PARTITIONS=1, REPLICAS=1, VALUE_FORMAT='JSON'); 

CREATE STREAM IF NOT EXISTS output (PREVIOUS_UNIQUE_FIELD STRING KEY, PREVIOUS_LOCATION_ARRAY ARRAY<STRUCT<LONGITUDE STRING, LATITUDE STRING>>, data VARCHAR, location STRUCT<longitude VARCHAR, latitude VARCHAR>, keywords VARCHAR) WITH (KAFKA_TOPIC='results', PARTITIONS=1, REPLICAS=1, VALUE_FORMAT='JSON'); 

CREATE OR REPLACE TABLE previousData as SELECT EXTRACTJSONFIELD(keywords, '$.key') as unique_field, LATEST_BY_OFFSET(location, 1) as location_array FROM input GROUP BY EXTRACTJSONFIELD(keywords, '$.key'); 

CREATE STREAM finalData as SELECT previous.*, input.data as data, input.location as location, input.keywords as keywords FROM input LEFT JOIN previousData previous ON EXTRACTJSONFIELD(keywords, '$.key') = previous.unique_field; 

INSERT INTO output SELECT * FROM finalData WHERE CheckInside(PREVIOUS_LOCATION_ARRAY[0]->LONGITUDE, PREVIOUS_LOCATION_ARRAY[0]->latitude, '[[0.0,0.0],[1.1,0.0],[1.1,1.1],[0.0,1.1],[0.0,0.0]]') = true;

I have custom udf “CheckInside” which check if point is inside polygon

everything works good when I use something another to previous location (e.g. I use ‘0.5’, ‘0.5’ and it works)

I see data in the PREVIOUS_LOCATION_ARRAY array (objects with longitude and latitude), so it will be empty only for the first time when data arrives with the same keywords.key field

how I can access to that fields? need to check that object with the same keywords.key field was before in that square

thanks a lot for your answers

nevermind
found that I can’t access to the element when trying to get with index 0 (PREVIOUS_LOCATION_ARRAY[0]->latitude), but everything works when I get with index 1 (PREVIOUS_LOCATION_ARRAY[1]->latitude)

I don’t understand it, but it works

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.