How to map JSON_ARRAYAGG results with mybatis

Question

I can't map the results from JSON_ARRAYAGG function.

mybatis mapper:

<mapper namespace="com.test.mapper.UserEntityMapper">
    <resultMap id="userMap" type="UserVO">
        <id property="id" column="id" />
        <result property="username" column="username"/>
        <collection property="pictures" ofType="PictureVO">
            <id property="id" column="id" />
            <result property="location" column="location" />
        </collection>
    </resultMap>
    <select id="findUserById" parameterType="map" resultMap="userMap">
        SELECT
        users.id,
        users.username,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                "id",
                pic.id,
                "location",
                pic.location
            ) 
        ) AS pictures
        FROM
            users
            LEFT JOIN pictures pic ON users.id = pic.user_id 
        WHERE
            users.id = 1
    </select>
</mapper>

UserVO model:

@Getter
@Setter
@NoArgsConstructor
public class UserVO {
    private Long id;
    private String username;
    private List<PictureVO> pictures;
}

PictureVO model:

@Getter
@Setter
@NoArgsConstructor
public class PictureVO {
    private Long id;
    private String location;
}

Results from calling the API:

{
    "id": 1,
    "username": "connor",
    "pictures": [
        {
            "id": 1,
            "location": null
        }
    ]
}

As you can see, the pictures only have a id can be map correctly. And there are suppose 2 results in pictures, if I execute the sql directly, and here only 1 returned.

score 0 · Answer 1 · answered Jan 05 '21 at 11:01

0

you should set your method's ("findUserById") return type List and MyBatis will complete that automatically. Try to write it in mapper interface:

List<UserVo> findUserById();

answered Jan 05 '21 at 11:01

Dmitrii B

2,672
3
5
14

score 0 · Answer 2 · answered Jan 05 '21 at 22:27

MyBatis returns Java objects not JSON, so it may be your 'API' that converts the Java objects into JSON.
If I am right, you just need to do the normal MyBatis mapping.

Something like this:

<resultMap id="userMap" type="UserVO">
  <id property="id" column="id" />
  <result property="username" column="username"/>
  <collection property="pictures" ofType="PictureVO">
    <id property="id" column="picture_id" />
    <result property="location" column="location" />
  </collection>
</resultMap>

<select id="findUserById" parameterType="map" resultMap="userMap">
  SELECT
    users.id,
    users.username,
    pic.id picture_id,
    pic.location
  FROM
    users
    LEFT JOIN pictures pic ON users.id = pic.user_id
  WHERE
    users.id = 1
</select>

Note that pic.id needs an alias to distinguish the column from users.id in the result set.

score 0 · Answer 3 · answered Jun 23 '22 at 03:15

Use typeHandler. For instance:

<resultMap id="userMap" type="UserVO">
        <id property="id" column="id" />
        <result property="username" column="username"/>
        <result property="pictures" column="pictures" 
         typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"
        />
    </resultMap>

How to map JSON_ARRAYAGG results with mybatis

3 Answers3