How to get data from Query(data JSON)

Help Connectors and Pipelines
1

Hi,
I have following stored proc which I’m using in my kafka pipeline.

  1. Can I extract specific field from Query(data JSON)
  2. SCALAR() is not fetching the result.

CREATE OR REPLACE PROCEDURE CIMBA_DEV.proc_test_v1 (batch QUERY(data JSON))
AS
DECLARE input JSON ;

BEGIN

INSERT INTO CIMBA_DEV.test_json(data) values('{}');
input = SCALAR(batch);
INSERT INTO CIMBA_DEV.test_json(data) values(input);
INSERT INTO CIMBA_DEV.test_json(data) values('{"t1":"k1"}');

EXCEPTION
WHEN OTHERS THEN
INSERT INTO CIMBA_DEV.error_json(data) select data from batch;
END ;
$$

Thanks,
Santosh

2

The SCALAR builtin should be used in a stored procedure when it a query is expected to return a single row. Since I assume you are not inserting a single row, this would not be the correct thing to do. You could fix your stored procedure and make it more efficient by rewriting to just use insert select:

INSERT INTO CIMBA_DEV.test_json select data from batch;

3

Hi Rob,
Thank you for response. Here is what I need
I’m have kafka pipeline which uses above stored procedure to update the columns. Current procedure is just a starting point to which I will be adding additional business logic on how to update data.

  1. Since pipeline expects stored proc to have QUERY(data JSON) as the parameter. I need a way to convert QUERY(JSON ) to normal JSON datatype
    I need this conversion to use JSON_EXTRACT function to fetch some of the fields to extract and add business logic based on the values of data extracted from json fields.

so I need a way to convert QUERY(JSON) to JSON or please let me know is there any other alternate way to extract specific fields .

Thanks,
Santosh

1 Like
4

QUERY(json) means that batch is a query variable that returns rows of json type. To get the json out, you can query the batch variable as I did in the previous example. You can also extract json keys directly from the json data as you would normally:

select data::key1, data::key2 from batch
5

As of version 6.7, MemSQL LOAD DATA and Pipelines can natively handle extracting specific fields from a stream of JSON values. And you might be able to express your processing of those fields as expressions in a LOAD DATA / Pipelines SET clause.

To turn on JSON mode, add format json to your Pipeline’s as load data clause and specify a desired keypath → column/variable mapping as described below: