JSON Insertar en una tabla MySQL o actualizar si existe
Esta es la continuación deInsertar en una tabla MySQL o actualizar si existe, pero esta vez quiero actualizar la entrada json en mysql
Abajo está el esquema:
CREATE TABLE TAG_COUNTER (
account varchar(36) NOT NULL,
time_id INT NOT NULL,
counters JSON,
PRIMARY KEY (account, time_id)
)
os datos de @example son como a continuación.
"google", "20180510", "{"gmail_page_viewed" : 12000300, "search_page_viewed" : 9898884726423}"
En realidad, siempre quiero actualizar (incrementar el contador) de esta tabla. pero todos los días start time_id (aaaaMMdd) es nuevo, por lo tanto, el contador json no tiene la clave de contador (por ejemplo,gmail_page_viewed
).
Así que la solución que quiero es crear una nueva columna de clave json convalue = 1
cuando no existe. p.ej. @{"gmail_page_viewed" : 1}
if counter (por ejemplo,gmail_page_viewed
) existe, luego incremente lavalue = value + 1
Así que ayúdame en una consulta de inserción con actualización si existe el contador json.
UPDATE_1 (INFORMACIÓN: siempre use IFNULL cuando trabaje con json)
He usado la solución sugerida por @wchiquito pero veo el siguiente problema.
Muy primera vez (después de la creación del esquema); Probé el siguiente sql
INSERT INTO `TAG_COUNTER`
(`partner_id`, `time_id`, `counters`)
VALUES
('google', '20180510', '{"gmail_page_viewed": 1}')
ON DUPLICATE KEY UPDATE `counters` =
JSON_SET(`counters`,
'$.gmail_page_viewed',
JSON_EXTRACT(`counters`,
'$.gmail_page_viewed') + 1
);
y obtuvo la respuesta a continuación; que es correcto como se esperaba.
'google', '20180510', '{"gmail_page_viewed": 1}'
Entonces intentó con un contador diferente en la siguiente consulta,
INSERT INTO `TAG_COUNTER`
(`account`, `time_id`, `counters`)
VALUES
('google', '20180510', '{"search_page_viewed": 1}')
ON DUPLICATE KEY UPDATE `counters` =
JSON_SET(`counters`,
'$.search_page_viewed',
JSON_EXTRACT(`counters`,
'$.search_page_viewed') + 1
);
y obtuve el siguiente.
'google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": null}'
¿Alguna idea de por qué este nuevo contador search_page_viewed está configurado con NULL ..?
ACTUALIZACIÓN_2 (INFORMACIÓN: Use comillas dobles alrededor de la tecla cuando el número se usa como clave)
También he enfrentado el siguiente problema, y lo resolví a partir de esa respuesta
UPDATE_3 (Información: Agrupe por, asegúrese de DONDE con "NOT NULL" o "> 0"
Obtener el recuento por período de tiempo (por ejemplo, por día, mes, año)
SELECT
SUBSTRING(time_id, 1, 6) AS month,
SUM(counters->>'$.gmail_page_viewed')
FROM TAG_COUNTER
WHERE counters->>'$.gmail_page_viewed' > 0
GROUP BY month;
ACTUALIZACIÓN_4 (Pregunta:)
Me gustaría ver la página de tendencias por hora (hora / día / mes / año) De la consulta a continuación puedo obtener el recuento por hora, pero solo si conozco la clave (gmail_page_viewed).
SELECT
SUBSTRING(time_id, 1, 6) AS month,
SUM(counters->>'$.gmail_page_viewed')
FROM TAG_COUNTER
WHERE counters->>'$.gmail_page_viewed' > 0
GROUP BY month;
Pero lo que quiero es ¿cómo sé la tendencia general del recuento de páginas por vez? Así que necesito algo como a continuación, por favor ayuda.
group by SUBSTRING(time_id, 1, 6) AS month, KEY
order by SUM(counters->>'$.KEY')