CODEWITHSUNDEEP
mysql
Dally
Dally

Reputation: 1427

MySQL - Create table with virtual columns based on conditions

I'm trying to create a table that contains indexed virtual columns that are referencing a JSON.

I've created the table which contains a virtual column named "amount". The problem is that the JSON doesn't always contain the key "amount". Sometimes it's named "presentationAmount".

Is it possible to put a condition in place for this?

When the JSON contains the key "threeDSecure" that's when "presentationAmount" is used, otherwise "amount" is used.

Here's my create table code:

CREATE TABLE transactions (
    id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    json JSON DEFAULT NULL,
    type VARCHAR(12) GENERATED ALWAYS AS (json->>"$.transaction.type"),
    uuid VARCHAR(32) GENERATED ALWAYS AS (json->>"$.transaction.payload.id"),
    holder VARCHAR(20) GENERATED ALWAYS AS (json->>"$.transaction.payload.card.holder"),
    amount DECIMAL(11,2) GENERATED ALWAYS AS (json->>"$.transaction.payload.amount"),
    resultCode VARCHAR(11) GENERATED ALWAYS AS (json->>"$.transaction.payload.result.code"),
    processingTime DATETIME GENERATED ALWAYS AS (json->>"$.transaction.payload.timestamp"),
    paymentType VARCHAR(2) GENERATED ALWAYS AS (json->>"$.transaction.payload.paymentType"),
    paymentBrand VARCHAR(20) GENERATED ALWAYS AS (json->>"$.transaction.payload.paymentBrand"),
    eci INT(2) GENERATED ALWAYS AS (json->>"$.transaction.payload.eci"),
    recurringType VARCHAR(9) GENERATED ALWAYS AS (json->>"$.transaction.payload.recurringType"),
    clearingInstitute VARCHAR(30) GENERATED ALWAYS AS (json->>"$.transaction.payload.resultDetails.clearingInstituteName"),
    merchantTransactionId VARCHAR(64) GENERATED ALWAYS AS (json->>"$.transaction.payload.merchantTransactionId"),
    divisionName VARCHAR(32) GENERATED ALWAYS AS (json->>"$.division.name"),
    divisionUuid VARCHAR(32) GENERATED ALWAYS AS (json->>"$.division.uuid"),
    merchantName VARCHAR(32) GENERATED ALWAYS AS (json->>"$.merchant.name"),
    merchantUuid VARCHAR(32) GENERATED ALWAYS AS (json->>"$.merchant.uuid"),
    channelName VARCHAR(32) GENERATED ALWAYS AS (json->>"$.channel.name"),
    channelUuid VARCHAR(32) GENERATED ALWAYS AS (json->>"$.channel.uuid"),
    INDEX typeIndex (type),
    INDEX idIndex (uuid),
    INDEX holderIndex (holder),
    INDEX amountIndex (amount),
    INDEX resultCodeIndex (resultCode),
    INDEX timestampIndex (processingTime),
    INDEX paymentTypeIndex (paymentType),
    INDEX paymentBrandIndex (paymentBrand),
    INDEX recurringTypeIndex (recurringType),
    INDEX clearingInstituteIndex (clearingInstitute),
    INDEX merchantTransactionIdIndex (merchantTransactionId),
    INDEX divisonNameIndex (divisionName),
    INDEX divisionUuidIndex (divisionUuid),
    INDEX merchantNameIndex (merchantName),
    INDEX merchantUuidindex (merchantUuid),
    INDEX channelNameIndex (channelName),
    INDEX channelUuidIndex (channelUuid)
) ENGINE=INNODB;

Upvotes: 1

Views: 249

Answers (1)

Barmar
Barmar

Reputation: 781716

You can use an IF() expression.

amount DECIMAL(11,2) GENERATED ALWAYS AS 
    (IF(JSON_CONTAINS_PATH(json, 'one', '$.transaction.payload.threeDSecure'), 
        json->>"$.transaction.payload.presentationAmount", 
        json->>"$.transaction.payload.amount")),

See the documentation of JSON_CONTAINS_PATH() for details of its use.

Upvotes: 2

Related Questions