feat: migrate metadata columns to JSONB and add JSONPath filtering#378
Merged
haritzsaiz merged 9 commits intomainfrom Jan 14, 2026
Merged
feat: migrate metadata columns to JSONB and add JSONPath filtering#378haritzsaiz merged 9 commits intomainfrom
haritzsaiz merged 9 commits intomainfrom
Conversation
Codecov Report❌ Patch coverage is
Additional details and impacted files@@ Coverage Diff @@
## main #378 +/- ##
=======================================
Coverage 46.77% 46.78%
=======================================
Files 146 146
Lines 8616 8625 +9
=======================================
+ Hits 4030 4035 +5
- Misses 4157 4160 +3
- Partials 429 430 +1
Flags with carried forward coverage won't be shown. Click here to find out more. ☔ View full report in Codecov by Sentry. 🚀 New features to boost your workflow:
|
Contributor
There was a problem hiding this comment.
Pull request overview
This pull request migrates metadata columns from text to JSONB format across four database tables (ca_certificates, certificates, devices, and dms) and introduces JSONPath filtering capabilities. The changes enable more flexible and efficient JSON queries on metadata fields.
Changes:
- Added database migrations to convert metadata columns from text to JSONB in ca_certificates, certificates, devices, and dms tables with bidirectional migration support
- Introduced JsonFilterFieldType and JsonPathExpression filter operation to enable JSONPath-based filtering
- Added comprehensive migration tests validating correct data conversion and JSONB functionality
Reviewed changes
Copilot reviewed 10 out of 10 changed files in this pull request and generated 2 comments.
Show a summary per file
| File | Description |
|---|---|
| engines/storage/postgres/utils.go | Added JsonPathExpression case to support JSONB @@ operator in SQL WHERE clauses |
| engines/storage/postgres/migrations_test/dms_test.go | Added comprehensive migration tests for dms table metadata conversion |
| engines/storage/postgres/migrations_test/devicemanager_test.go | Added comprehensive migration tests for devices table metadata conversion |
| engines/storage/postgres/migrations_test/ca_test.go | Added comprehensive migration tests for ca_certificates and certificates table metadata conversion |
| engines/storage/postgres/migrations/dmsmanager/20251217120000_metadata_text_to_jsonb.sql | Migration script to convert dms metadata column to JSONB |
| engines/storage/postgres/migrations/devicemanager/20251217120000_metadata_text_to_jsonb.sql | Migration script to convert devices metadata column to JSONB |
| engines/storage/postgres/migrations/ca/20251217120000_metadata_text_to_jsonb.sql | Migration script to convert ca_certificates and certificates metadata columns to JSONB |
| core/pkg/resources/query.go | Added JsonFilterFieldType and JsonPathExpression constants |
| core/pkg/resources/careq.go | Added metadata as JsonFilterFieldType to KMSFilterableFields |
| backend/pkg/controllers/utils.go | Added JSONPath filter handling with URL unescaping and net/url import |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
… query unescaping errors
…id operands Signed-off-by: Juanjo Rodriguez <[email protected]>
- Implemented unit tests for filtering devices by metadata using JSONPath expressions in `device_filter_tests.go`. - Added comprehensive filtering tests for DMS entities based on metadata and settings in `dms_filter_test.go`. - Created key filtering tests that utilize JSON metadata in `key_filter_test.go`. - Updated filterable fields in `fields.go` to include metadata and settings for DMS and devices. - Migrated DMS settings column from text to JSONB format in the database for better querying capabilities. - Enhanced SDK query parameter encoding to support JSONPath expressions. Signed-off-by: Juanjo Rodriguez <[email protected]>
Signed-off-by: Juanjo Rodriguez <[email protected]>
Signed-off-by: Juanjo Rodriguez <[email protected]>
Signed-off-by: Juanjo Rodriguez <[email protected]>
Signed-off-by: Juanjo Rodriguez <[email protected]>
|
jjrodrig
approved these changes
Jan 14, 2026
This was referenced Jan 29, 2026
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
This pull request introduces support for storing and querying
metadatacolumns as JSONB in several tables, enabling more flexible and efficient JSON queries. It includes database migrations to convert existingmetadatacolumns from text to JSONB, updates to the filtering/query logic to support JSONPath expressions, and comprehensive migration tests to ensure correctness.Database migrations:
metadatacolumn fromtexttojsonbin theca_certificates,certificates,devices, anddmstables, with support for both upgrade and downgrade paths. [1] [2] [3]Query and filter enhancements:
JsonFilterFieldTypeandJsonPathExpressionfilter operation to support JSONPath-based filtering on JSONB columns. [1] [2] [3] [4]metadataas a filterable field of typeJsonFilterFieldTypein the relevant resource definitions.Testing and validation:
metadatavalues (including handling of empty and null values) and to verify that JSONB operators can be used in queries post-migration. [1] [2] [3]Dependency updates:
net/urlfor query unescaping in filter logic.