Using JSON fields in INSERT VALUES() expression

Off-Topic
1

Hello!

It turned out that I can’t refer to JSON fields in an INSERT statement when using a cursor in a stored procedure:
Let’s assume that r is a cursor, which refers to a row with the only JSON field “rec”.
rec contains the following record: {“inner_field”: “testvalue”}

Then the following statement would not work:
INSERT INTO tbl VALUES(r.rec::$inner_field)

Instead, it would raise an exception:
image

Could anyone please explain why it’s not possible to refer to json fields like that?

Regards,
Denis

2

Hi Denis,

I assume you are using COLLECT to store the query results in an array? If so, try copying r.rec into a new variable and then try your INSERT statement using that new variable.

The example here demonstrates copying into a new variable when using COLLECT:

This is done on the lines:
_id = x.id;
_name = x.name;

Scott

3

Tangential question: what does your stored procedure look like?

I ask because if you’re just using it to extract sub-values from a stream of JSON objects and insert them into a single table, native JSON pipelines with CREATE PIPELINE … FORMAT JSON (perhaps with a SET clause for transforming data) would be faster and likely easier to set up. Even if you do need special stored-procedure-only logic, it’ll generally be more efficient to use FORMAT JSON to extract sub-values from input JSON objects and pass them as structured data to the stored procedure for further processing.

4

Hi @sasha,

Please see the procedure code below:

Procedure code

CREATE OR REPLACE PROCEDURE cdc_s_srv_req_ct_3(batch query(rec JSON NULL)) RETURNS void AS
DECLARE
_ROW_ID varchar(40);
_SR_NUM varchar(40);
_SR_STAT_ID varchar(255);
_SR_AREA varchar(255);
_SR_PRIO_CD varchar(40);
_SR_SEV_CD varchar(40);
_X_SR_AREA_LIC varchar(255);
_SR_SUB_STAT_ID varchar(40);
_CREATED datetime;

BEGIN
FOR r IN collect(batch) LOOP
IF r.rec::$ATT_CHANGE_OPER = ‘D’ then
#RAISE user_exception(“other exception”);
_ROW_ID = r.rec::$ROW_ID;
DELETE FROM s_srv_req_ct_3 WHERE ROW_ID = _ROW_ID;
ELSE

        _ROW_ID = r.rec::$ROW_ID;
        _SR_NUM = r.rec::$SR_NUM;
		_SR_STAT_ID = r.rec::$SR_STAT_ID;
		_SR_AREA = r.rec::$SR_AREA;
		_SR_PRIO_CD = r.rec::$SR_PRIO_CD;
		_SR_SEV_CD = r.rec::$SR_SEV_CD;
		_X_SR_AREA_LIC = r.rec::$X_SR_AREA_LIC;
		_SR_SUB_STAT_ID = r.rec::$SR_SUB_STAT_ID;
		_CREATED = to_date(r.`rec`::$CREATED,'DD.MM.YYYY HH24:MI:SS');
	
        insert into s_srv_req_ct_3 
					(ROW_ID, SR_NUM, SR_STAT_ID, SR_AREA, SR_PRIO_CD, SR_SEV_CD, X_SR_AREA_LIC, 
					SR_SUB_STAT_ID, CREATED, ins_upd_ts) 
					values 
					(_ROW_ID, _SR_NUM, _SR_STAT_ID, _SR_AREA, _SR_PRIO_CD, _SR_SEV_CD, _X_SR_AREA_LIC, _SR_SUB_STAT_ID, _CREATED , now())
					on duplicate key update
					`SR_NUM` = COALESCE(VALUES(`SR_NUM`), s_srv_req_ct_3.`SR_NUM`),
					`SR_STAT_ID` = COALESCE(VALUES(`SR_STAT_ID`), s_srv_req_ct_3.`SR_STAT_ID`),
					`SR_AREA` = COALESCE(VALUES(`SR_AREA`), s_srv_req_ct_3.`SR_AREA`),
					`SR_PRIO_CD` = COALESCE(VALUES(`SR_PRIO_CD`), s_srv_req_ct_3.`SR_PRIO_CD`),
					`X_SR_AREA_LIC` = COALESCE(VALUES(`X_SR_AREA_LIC`), s_srv_req_ct_3.`X_SR_AREA_LIC`),
					`SR_SUB_STAT_ID` = COALESCE(VALUES(`SR_SUB_STAT_ID`), s_srv_req_ct_3.`SR_SUB_STAT_ID`),
					`CREATED` = COALESCE(VALUES(`CREATED`), s_srv_req_ct_3.`CREATED`),
					`ins_upd_ts` = now();
		
    END IF;
   
END LOOP;

END;

1 Like