skip to Main Content

I want to merge json data distributed over multiple rows in a mysql column via python sqlalchemy library. I was planning to use json_merge_preserve but unable to use over an entire column.

I used following code:

select(func.json_merge_preserve(db.mytable.c.myjsoncolumn))

But it says that the function need at least two parameters.

2

Answers


  1. Chosen as BEST ANSWER

    I have used the sql logic mentioned in danblack's answer and implemented directly in python.

    
        conn = engine.connect()
        qry_1 = select(db.mytable.c.myjsoncolumn).where(mycondition)
        res = conn.execute(qry_1).fetchall()
        qry_2 = select(func.json_merge_preserve('
                    {}', 
                   *[json.dumps(r['myjsoncolumn']) for r in res]
                ))
        res_2 = conn.execute(qry_2).fetchone()[0]
        # res_2 is the answer
        
    
    

    I am not sure of the performance implications of this solution.


  2. An example is to create the SQL function:

    DELIMITER //
    CREATE AGGREGATE FUNCTION json_merge_preserve_agg(j TINYTEXT) RETURNS TEXT DETERMINISTIC
    BEGIN
      DECLARE r TEXT;
      DECLARE CONTINUE HANDLER FOR NOT FOUND return r;
      LOOP
        FETCH GROUP NEXT ROW;
        IF r IS NULL THEN
          SET r = j;
        ELSE
          SET r = JSON_MERGE_PRESERVE(r, j);
        END IF;
      END LOOP;
    END //
    

    Some sample data:

    create table j (t text);
    
    insert into j values ('[1, 2]'),('[2, 4]');
    

    Use the function to return results:

     select json_merge_preserve_agg(t) from j;
    

    Using the function from sql alchemeny is like this answer:

     from sqlalchemy.sql.expression import func
    
     result = conn.execute(select(func.json_merge_preserve_agg(db.mytable.c.myjsoncolum)))
    

    ref: fiddle

    ref: aggregate functions of MariaDB

    Note: MariaDB-10.3+ only, MySQL doesn’t have aggregate (non-UDF) functions.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search