How to Parse Json in Kinesis Analytics SQL query

Question

How to Parse Json in Kinesis Analytics SQL query.

I have streaming data received from Kinesis Stream, in column I have json wanted to ready some elements of Json

Ex. In column body I have below Json

{"deviceStatus":"Active", "deviceId":"11111"}

I want to extract deviceStatus from json, something like below

CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( "deviceStatus" VARCHAR(24));
CREATE OR REPLACE PUMP "STREAM_PUMP" AS 
INSERT INTO "DESTINATION_SQL_STREAM" 
SELECT STREAM "body"."deviceStatus" FROM "SOURCE_SQL_STREAM_001";

Answer 1

I have generated a schema with 2 columns - deviceStatus - deviceId

And I mapped each json element to each column as explained in below articles.

https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sch-mapping.html#sch-mapping-json

https://docs.aws.amazon.com/kinesisanalytics/latest/dev/about-json-path.html

And used them in SQL query as below

 CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( "deviceId" VARCHAR(16), "deviceStatus" VARCHAR(24));
 CREATE OR REPLACE PUMP "STREAM_PUMP" AS 
 INSERT INTO "DESTINATION_SQL_STREAM" 
 SELECT STREAM "deviceId", "deviceStatus" FROM "SOURCE_SQL_STREAM_001";