code

MySql JSON Encoded Column Notes

Robin Michael

Jan 31, 2024 • 1 min read

Need to extract a search against a JSON encoded column in MySql and then update from one JSON property to another in that encoded column (TargetTable.RelatedItems) JsonSourceProperty -> TargetProperty

SELECT JSON_SET(RelatedItems, '$.JsonTargetProperty', JSON_EXTRACT(RelatedItems, '$.JsonSourceProperty'))
FROM TargetTable;

SELECT JSON_EXTRACT(RelatedItems, '$.JsonTargetProperty'), Id
FROM TargetTable;

SELECT Id, JSON_EXTRACT(RelatedItems, '$.JsonSourceProperty'), JSON_EXTRACT(RelatedItems, '$.JsonTargetProperty')
FROM TargetTable
where JSON_EXTRACT(RelatedItems, '$.JsonSourceProperty') IS NOT NULL;

UPDATE TargetTable
SET RelatedItems = JSON_SET(RelatedItems, '$.JsonSourceProperty', JSON_EXTRACT(RelatedItems, '$.JsonTargetProperty'));

UPDATE TargetTable
SET RelatedItems = JSON_SET(RelatedItems, '$.JsonTargetProperty', JSON_EXTRACT(RelatedItems, '$.JsonSourceProperty'));

UPDATE TargetTable
SET RelatedItems = JSON_REMOVE(RelatedItems, '$.JsonSourceProperty');

MySQL :: MySQL 5.7 Reference Manual :: 12.10 Full-Text Search Functions

Sign up for more like this.