With MySQL 8.0 the json_arrayagg was introduced, this made it possible to aggregate json results.
Now I want to use it to show the tags attached to a message.
Currently there are three tables for this (simplefied)
CREATE TABLE IF NOT EXISTS feed_message (
id CHAR(36) PRIMARY KEY
CREATE TABLE IF NOT EXISTS feed_tag (
id CHAR(36) PRIMARY KEY,
name VARCHAR(30) NOT NULL
CREATE TABLE IF NOT EXISTS feed_message_tag (
message CHAR(36) NOT NULL,
tag CHAR(36) NOT NULL,
PRIMARY KEY (message, tag)
So there is one table contain the message, one that holds all the tags and a table that hold the connections between tags and messages (feed_message_tag).
The response I am seeking is a list of messages with a column of tags which is an array of objects with there id + name. So something as followed
[{"id": 1, "name": "Foo"}]
Now the examples I find use (inner) joins which means a message must have a tag, but that is not always the case so left join is used. Which brings me to the following query I use
SELECT
json_arrayagg(
json_object(
'id',
ft.id,
'name',
ft.name
) as 'tags'
FROM feed_message fm
LEFT JOIN feed_message_tag fmt ON fmt.message = fm.id
LEFT JOIN feed_tag ft ON fmt.tag = ft.id
GROUP BY fm.id
The problem now is that if one message has no tags I get the following output as tags.
[{"id": null, "name": null}]
After some searching and tweaking I came to the following change for the tags column
fmt.message IS NULL,
json_array(),
json_arrayagg(
json_object(
'id',
ft.id,
'name',
ft.name
) as 'tags'
Is this the intended behaviour or am I doing something wrong?
Seems like your method may be the only way to do this.
The reason is that NULL is a valid value to include in JSON objects. While most aggregation functions ignore nulls, so they properly ignore non-matching rows that come from LEFT JOIN, it would be a problem for JSON_ARRAYAGG(). It would prevent you from including null values in other situations. There's no way to distinguish explicit nulls from LEFT JOIN nulls.
With MySQL 8.0 the json_arrayagg was introduced, this made it possible to aggregate json results.Now I want to use it to show the tags attached to a message.Currently there are three tables for this (...
文章目录第六章 SQL聚合函数
JSON
_
ARRAY
AGG
大纲参数描述包含转义字符的数据值最大
JSON
数组大小
JSON
_
ARRAY
AGG
和 %SelectMode
JSON
_
ARRAY
AGG
和ORDER BY相关的聚合函数示例
第六章 SQL聚合函数
JSON
_
ARRAY
AGG
创建
JSON
格式值数组的聚合函数。
注：IRIS可用，IRIS之前版本不可用。
JSON
_
ARRAY
AGG
([ALL | DISTINCT [BY(col-
list
)]] string-expr [%FOREACH(col-l
慢查询优化的基本步骤
1)先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2）where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3)explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4)order by limit 形式的sql语句让排序的表优先查...
当使用
JSON
解析大量数组数据时，有可能出现值为空，或者值不是数组，键不存在的各种坑这就需要加上一下的三个判断条件了
如：
JSON
Object
json
Object=
JSON
Object.fromObject(result);
JSON
Array
firstArr=
json
Object.get
JSON
Array
("pois");有可能报无法解析
JSON
Arry的异常，这就需要加上判断条件了
原代码如下：
JSON
Array
zzjgxx
Array
=
json
object.get
JSON
Array
("zzjgxx");
if(!zzjgxx
Array
.isEmpty()&&zzjgxx
Array
.size()>=1) {
后来理解了
JSON
Array
判空最好不用isEmpty, 因为I
string_
agg
，
array
_
agg
这两个函数的功能大同小异，只不过合并数据的类型不同。
https://www.postgresql.org/docs/9.6/static/functions-
agg
regate.html
array
_
agg
(expression)
把表达式变成一个数组 一般配合
array
_to_string() 函数使用string_
agg
(expression, de
对于这两条SQL，老哥们一眼就看出来了，就是如果判断的区别，恭喜你，答对了，确实是一个如果的判断区别。
如果的判断如果当我们的中值是一个空列表的时候，系统会出现报错，所以我们需要加上一个。
注意：1，如果判断的时候，不仅仅要判断是否为空，还要判断大小的长度。
2，和条件的空格，也要注意一下，如果空格有问题，会...
