Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I want to separate the JSON from different columns of my database into new columns using a CTE, but I always get the same error

From DBeaver Community, I access my database and create a new SQL query. I write the corresponding code for the CTE I want to create, but it constantly shows a syntax error. I’ve tried everything, but I can’t find the error. If anyone can help me, it would be greatly appreciated. Thank you very much.

The Query I used is this:

— CTE Json

WITH json_extract AS (
    SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(`from`, '$.id')) AS from_id,
        JSON_UNQUOTE(JSON_EXTRACT(`from`, '$.name')) AS from_name,
        JSON_UNQUOTE(JSON_EXTRACT(`shares`, '$[0].count')) AS shares_count,
        JSON_UNQUOTE(JSON_EXTRACT(`properties`, '$[0].text')) AS properties_length,
        JSON_UNQUOTE(JSON_EXTRACT(`admin_creator`, '$.id')) AS admin_creator_id,
        JSON_UNQUOTE(JSON_EXTRACT(`admin_creator`, '$.link')) AS admin_creator_link
    FROM raw_post
)

SELECT *

FROM json_extract;

And the error I get is this one:

SQL Error [1064][42000]: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near " at line 10

The dbms version I´m using is MySQL: 8.0.35

I need to extract each value from every column that has a json into a new column.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I don´t know what the problem was, but it actually worked yesterday using the same query. Maybe i typed something wrong without noticing, anyways! Thank you so much for your help!


  2. 0

    I think you’re doing wrong something as no sample data available there.

    I’m using MySQL 8.0.35 in my local system and try your query with some dummy data. It’s working fine my end.

    According to you query there are atleast 4 columns in table: raw_post as follows:

    from           ➫ JSON_OBJECT which contains keys: "id" & "name" 
shares         ➫ JSON_ARRAY of JSON_OBJECTS with keys: "count"
properties     ➫ JSON_ARRAY of JSON_OBJECTS with keys: "text"
admin_creator  ➫ JSON_OBJECT which contains keys: "id" & "link"

    So, my sample data table: raw_post is:
    enter image description here

    Output:
    enter image description here

    Working fine in MySQL 8.3.0
    Sample Code: MyCompiler

    If your data type is OK then, it should not throw such error.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search