How can i map entity column to json Key in Spring Data JDBC

Question

this is my entity

@Table("amazon_rds_inventory")
public class AmazonDBInstanceDataSelect {
    @Id
    @Column("id")
    private long id;

    @Column("inventory_metadata")
    private String inventoryMetadata;

    @Column("aws_identifier")
    private String awsIdentifier;

    @Column("host")
    private String host;

    @Column("region")
    private String region;

    @Column("type")
    private AmazonInventoryType amazonInventoryType;

    @Column("created_by")
    private String user;

    @Column("created_on")
    private Timestamp createdOn;

    @Column("cloned")
    private boolean cloned;

    @Column("inventory_metadata->>'$.dbclusterIdentifier' as clusterIdentifier")
    private String clusterIdentifier;

    @Column("deleted")
    private boolean deleted;

}

json data of column "inventory_metadata"

{
"port": 27017,
"engine": "docdb",
"status": "available",
"multiAZ": false,
"tagList": [],
"capacity": null,
"endpoint": "nimesa-docdb-cluster-01.cluster-cjzyjmw4obtv.us-east-1.docdb.amazonaws.com",
"kmsKeyId": "arn:aws:kms:us-east-1:220709770714:key/293f9b17-43fc-4946-92d6-cfcb9965a8cd",
"engineMode": "provisioned",
"cloneGroupId": null,
"databaseName": null,
"dbclusterArn": "arn:aws:rds:us-east-1:220709770714:cluster:nimesa-docdb-cluster-01",
"hostedZoneId": "ZNKXH85TT8WVW",
"dbsubnetGroup": "custom-subnetgroupwith-custom",
"engineVersion": "4.0.0",
"masterUsername": "nimesa",
"readerEndpoint": "nimesa-docdb-cluster-01.cluster-ro-cjzyjmw4obtv.us-east-1.docdb.amazonaws.com",
"associatedRoles": [],
"backtrackWindow": null,
"customEndpoints": [],
"percentProgress": null,
"allocatedStorage": 1,
"characterSetName": null,
"dbclusterMembers": [
    {
        "clusterWriter": true,
        "promotionTier": 1,
        "isClusterWriter": true,
        "dbinstanceIdentifier": "nimesa-docdb-01",
        "dbclusterParameterGroupStatus": "in-sync"
    }
],
"storageEncrypted": true,
"availabilityZones": [
    "us-east-1f",
    "us-east-1b",
    "us-east-1a"
],
"clusterCreateTime": 1615532359067,
"crossAccountClone": false,
"domainMemberships": [],
"vpcSecurityGroups": [
    {
        "status": "active",
        "vpcSecurityGroupId": "sg-05c8ed6d9836007fd"
    }
],
"activityStreamMode": null,
"copyTagsToSnapshot": false,
"deletionProtection": false,
"dbClusterResourceId": "cluster-6OQQJU32K2ZZE2GSTHP2DHNGZM",
"dbclusterIdentifier": "nimesa-docdb-cluster-01",
"httpEndpointEnabled": false,
"activityStreamStatus": "stopped",
"latestRestorableTime": 1620666452774,
"backupRetentionPeriod": 1,
"earliestBacktrackTime": null,
"pendingModifiedValues": null,
"preferredBackupWindow": "00:00-00:30",
"activityStreamKmsKeyId": null,
"earliestRestorableTime": 1620666452774,
"readReplicaIdentifiers": [],
"dbclusterParameterGroup": "default.docdb4.0",
"scalingConfigurationInfo": null,
"preferredMaintenanceWindow": "wed:07:48-wed:08:18",
"globalWriteForwardingStatus": null,
"replicationSourceIdentifier": null,
"enabledCloudwatchLogsExports": [],
"backtrackConsumedChangeRecords": null,
"globalWriteForwardingRequested": null,
"activityStreamKinesisStreamName": null,
"dbclusterOptionGroupMemberships": [],
"iamdatabaseAuthenticationEnabled": false

}

the error i am getting:- java.sql.SQLSyntaxErrorException: Unknown column 'amazon_rds_inventory.inventory_metadata->>'$.dbclusterIdentifier' as clusterIdentifier' in 'field list'

I want to get the value of dbclusterIdentifier key inside the json and map it to my entity

I'll need some time to craft a proper answer, but as a quick pointer: `@Column` won't work. You'll need at least a Custom Conversion — Jens Schauder, Jun 28 '21 at 12:11
no issue, for now, I am using Mysql native query in @Query annotation for parsing JSON data directly from DB — Aman Kashyap, Jun 29 '21 at 13:03

score 1 · Answer 1 · answered Jun 29 '21 at 13:38

Depending on the exact goals you have various options.

Let's start with why the current approach can't work.

@Column specifies a column, not an arbitrary expression. Setting it to an expression results just in a very weird column name, as you noted.
Even if it could handle expressions, it would not only need an expression to extract the value, but also one with which to write a value back.
The field contains only part of the document. If it would work, writing that value back would replace the original JSON document with a document containing just that value.

Let's look at a couple of solutions:

In your current setup you already load the complete JSON document as inventoryMetadata. I'd therefore recommend to post process the entity after loading. Register an AfterLoadCallback in your application context, that extracts the desired value from inventoryMetadata into clusterIdentifier
If you really want to access the JSON document in a structured way you could have a custom type, say InventoryMetadata and register converters to convert it to a String and back, which if I remember correctly should work with the SQL standard, so hopefully it does with MySQL.
If you want to keep the JSON handling out of the Java code, you could have a database view in the database and do any extraction/manipulation there. You can map your entity against that view. If the view is updatable you may even use if for writing the data back.
The solution you describe of using a dedicated @Query is another option, that works fine when you only want to read the data.

How can i map entity column to json Key in Spring Data JDBC

1 Answers1