Hi,
I just can't seem to get the following to work. I want to parse all “aspects” from the JSON attached. It's an answer from Chat GTP API. Each aspect should result in a row and list it's attributes (summary, sentiment, affect…). I tried all kinds of variations, asked Chat GTP etc. does not seem to work.
JSON:
{
"id": "chatcmpl-8y06bKIVY0sH8ABTaUBJ96L2Brt6g",
"object": "chat.completion",
"created": 1709310777,
"model": "gpt-3.5-turbo-0125",
"choices": [
{
"index": 0,
"message": {
"role": "assistant",
"content": "{\n\"aspects\": [\n{\"aspect\": \"Design\", \"summary\": \"The car has a cheerful and happy design with a smiley face on the road, which stands out in the world of mean and angular modern car designs.\", \"sentiment\": \"positive\", \"affect\":\"Exterior\"},\n{\"aspect\": \"Practicality\", \"summary\": \"The car offers enormous practicality with a ridiculous amount of cabin space, a large boot, and an overall focus on providing a versatile and spacious interior.\", \"sentiment\": \"positive\", \"affect\": \"Interior, Functionality\"},\n{\"aspect\": \"Features\", \"summary\": \"The car is equipped with modern features such as powered rear sliding doors, a double cup holder, USB ports, adjustable center console, and various storage compartments for convenience.\", \"sentiment\": \"positive\", \"affect\": \"Interior, Convenience\"},\n{\"aspect\": \"Driving Experience\", \"summary\": \"The driving experience is described as joyous, offering a delightful driving position with a spectacular view, cocooned in glass, and a sense of authority on the road, especially with the high riding position and spacious interior.\", \"sentiment\": \"positive\", \"affect\": \"Driving, Comfort\"},\n{\"aspect\": \"Efficiency\", \"summary\": \"Despite its shape, the car demonstrates relatively efficient performance, offering good range and speed charging, making it suitable for a wide range of activities and driving scenarios.\", \"sentiment\": \"positive\", \"affect\": \"Performance, Environment\"},\n{\"aspect\": \"Price\", \"summary\": \"The reviewer points out that the price of the car, starting at 57,000 pounds, may be a significant consideration for potential buyers, especially when compared to other options in the market.\", \"sentiment\": \"negative\", \"affect\": \"Affordability\"}\n],\n\"sub_id\": \"(50200)\"\n}"
},
"logprobs": null,
"finish_reason": "stop"
}
],
"usage": {
"prompt_tokens": 4255,
"completion_tokens": 382,
"total_tokens": 4637
},
"system_fingerprint": "fp_2b778c6b35"
}
e.g. SQL:
SELECT j.*
FROM tbl_openai_data t,
JSON_TABLE(t.answer_data, '$.choices[0].message.content'
COLUMNS (
nested PATH '$.aspects[*]'
COLUMNS(
aspect VARCHAR2(4000) PATH '$.aspect',
summary VARCHAR2(4000) PATH '$.summary',
sentiment VARCHAR2(4000) PATH '$.sentiment',
affect VARCHAR2(4000) PATH '$.affect'
)
)
) j;
HEEEELP :)