Pacific-Design.com

    
Home Index

1. MariaDB

2. JSON

MariaDB / JSON /

MariaDB JSON Analytics for Facebook

-- Remove quotes
update total_video_reactions_by_type_total set json = REPLACE(json, '\'', '');

-- Open quote
update total_video_reactions_by_type_total set json = REPLACE(json, '{', '{"');

-- Close quote
update total_video_reactions_by_type_total set json = REPLACE(json, '}', '"}');

-- Begin quote
update total_video_reactions_by_type_total set json = REPLACE(json, ': ', '": "');

-- End quote
update total_video_reactions_by_type_total set json = REPLACE(json, ', ', '", "');

-- Specific to MySQL, unavailable on MariaDB
-- Dislikes
select JSON_EXTRACT(json,'$.sorry') + JSON_EXTRACT(json,'$.anger') as dislikes from total_video_reactions_by_type_total limit 10;

-- Likes
select JSON_EXTRACT(json,'$.like') + JSON_EXTRACT(json,'$.love') + JSON_EXTRACT(json,'$.haha') as likes from total_video_reactions_by_type_total limit 10;

-- Both
SELECT 
  JSON_EXTRACT(json,'$.sorry') +
  JSON_EXTRACT(json,'$.anger') as dislikes,
  JSON_EXTRACT(json,'$.like') +
  JSON_EXTRACT(json,'$.love') +
  JSON_EXTRACT(json,'$.haha') as likes
FROM total_video_reactions_by_type_total
LIMIT 10;