super data type not parsing properly

0

Trying below mentioned code using super data type. But its not parsing it. Please help me on this.

SELECT id,val,val."SS" FROM temp_json c, c.d AS val where id='0010a191-bf42-11ed-9178-ddc4f20ae2d7']

Table Script:- CREATE TABLE reporting_owner.temp_json ( id character varying(200) ENCODE lzo, d super ) DISTSTYLE AUTO; Out put attached:- Enter image description here

example data:- [{"SS":[{"data":[{"label":"checksum","value":"2553"},{"label":"numSettings","value":"10"}],"metadata":{"type":"SS"}}]},{"SHV":[{"data":[{"label":"rawPayload","value":"544d555f544553545630332c32315f30345f32325f3031"}]}]},{"TT":[{"data":[{"label":"trailerId","value":"0"},{"label":"highTempThreshold","value":"115"},{"label":"highPressureThreshold","value":"97"},{"label":"lowPressureThreshold","value":"48"}],"metadata":{"type":"TT"}}]},{"BS1":[{"data":[{"label":"frontRight","value":"58","units":"%","type":"Brake Pad Thickness Remaining"},{"label":"rearLeft","value":"92","units":"%","type":"Brake Pad Thickness Remaining"},{"label":"frontLeft","value":"15","units":"%","type":"Brake Pad Thickness Remaining"},{"label":"rearRight","value":"100","units":"%","type":"Brake Pad Thickness Remaining"}],"metadata":{"label":"BS1_0","index":"0","type":"BMS"}}]},{"BC1":[{"data":[{"label":"parkingBrakeStatus","value":"UNLOCK"},{"label":"frontLeftServiceBrakePressure","value":"69.00","units":"kPa","type":"Pressure"},{"label":"frontLeftParkingBrakePressure","value":"103.00","units":"kPa","type":"Pressure"},{"label":"frontRightServiceBrakePressure","value":"345.00","units":"kPa","type":"Pressure"},{"label":"frontRightParkingBrakePressure","value":"379.00","units":"kPa","type":"Pressure"},{"label":"rearLeftServiceBrakePressure","value":"552.00","units":"kPa","type":"Pressure"},{"label":"rearLeftParkingBrakePressure","value":"586.00","units":"kPa","type":"Pressure"},{"label":"rearRightServiceBrakePressure","value":"896.00","units":"kPa","type":"Pressure"},{"label":"rearRightParkingBrakePressure","value":"931.00","units":"kPa","type":"Pressure"}],"metadata":{"label":"BC1_0","index":"0","type":"BMS"}}]},{"SHS1":[{"data":[{"label":"rawPayload","value":"0001c607"}]}]},{"SHN":[{"data":[{"label":"sensorHubS/N","value":"6.14336.10496"}],"metadata":{"type":"SHN"}}]}]

Rajesh
asked a year ago236 views
1 Answer
1

Hello,

My understanding is that you are not able to parsing super type data. Please do correct me if my understanding varies from your query.

I conducted the test in the following format and confirmed that parsing works normally. I have corrected the syntax error in some parentheses in the JSON data form, and inform you that the data was parsed through JSON_PARSE.

I'm forwarding you my test query.

SET enable_case_sensitive_identifier TO true;

drop table testparse;

create table testparse(super_json super);

insert into testparse values(JSON_PARSE('{
    "SS": [{
        "data": [{
            "label": "checksum",
            "value": "2553"
        },
        {
            "label": "numSettings",
            "value": "10"
        }],
        "metadata": {
            "type": "SS"
        }
    }],
    "SHV": [{
        "data": {
            "label": "rawPayload",
            "value": "544d555f544553545630332c32315f30345f32325f3031"
        }
    }],
    "TT": [{
        "data": [{
            "label": "trailerId",
            "value": "0"
        },
        {
            "label": "highTempThreshold",
            "value": "115"
        },
        {
            "label": "highPressureThreshold",
            "value": "97"
        },
        {
            "label": "lowPressureThreshold",
            "value": "48"
        }],
        "metadata": {
            "type": "TT"
        }
    }],
    "BS1": [{
        "data": [{
            "label": "frontRight",
            "value": "58",
            "units": "%",
            "type": "Brake Pad Thickness Remaining"
        },
        {
            "label": "rearLeft",
            "value": "92",
            "units": "%",
            "type": "Brake Pad Thickness Remaining"
        },
        {
            "label": "frontLeft",
            "value": "15",
            "units": "%",
            "type": "Brake Pad Thickness Remaining"
        },
        {
            "label": "rearRight",
            "value": "100",
            "units": "%",
            "type": "Brake Pad Thickness Remaining"
        }],
        "metadata": {
            "label": "BS1_0",
            "index": "0",
            "type": "BMS"
        }
    }],
    "BC1": [{
        "data": [{
            "label": "parkingBrakeStatus",
            "value": "UNLOCK"
        },
        {
            "label": "frontLeftServiceBrakePressure",
            "value": "69.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "frontLeftParkingBrakePressure",
            "value": "103.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "frontRightServiceBrakePressure",
            "value": "345.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "frontRightParkingBrakePressure",
            "value": "379.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "rearLeftServiceBrakePressure",
            "value": "552.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "rearLeftParkingBrakePressure",
            "value": "586.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "rearRightServiceBrakePressure",
            "value": "896.00",
            "units": "kPa",
            "type": "Pressure"
        },
        {
            "label": "rearRightParkingBrakePressure",
            "value": "931.00",
            "units": "kPa",
            "type": "Pressure"
        }],
        "metadata": {
            "label": "BC1_0",
            "index": "0",
            "type": "BMS"
        }
    }],
    "SHS1": [{
        "data": {
            "label": "rawPayload",
            "value": "0001c607"
        }
    }],
    "SHN": [{
        "data": {
            "label": "sensorHubS/N",
            "value": "6.14336.10496"
        },
        "metadata": {
            "type": "SHN"
        }
    }]
}'));

select * from testparse;

Result :

select super_json, super_json."BS1" from testparse;
super_json	                            | BS1
{"SS":[{"data":[{"label":"che...   | [{"data":...

I trust that the above information will be useful for you. Please feel free to reach out to me if you have any questions or issues regarding this, and I will gladly assist with them as well.

profile pictureAWS
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions