Merge JSON in a mysql column using sqlalchemy

SetsunaCyan
March 28, 2023
76 views
1 vote
2 Answers

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.

Answers

Chosen as BEST ANSWER
- SetsunaCyan
- March 30, 2023 at 8:28 am
- 0 votes
0
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.

(Edit)

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)))