This does not cover data migration from a previous system into FOLIO!

Requirements: MODORDSTOR-89 - Getting issue details... STATUS

Once our first customers "go-live" and load data into the system, breaking schema changes will need to be handled w/o "starting fresh" or manually intervention.

Considered changes should be made by executing SQL queries directly to the PostgreSQL DB. For most modification scenarious there is no RMB functionality at the moment. Possible test scenarios are presented in drop-down lists.

1. Fields

1.1 Field has been renamed in a schema^*

RMB doesn't support field renaming existing field, but the following SQL query allows to rename JSONB field:

Snippet 1.1 - Field renaming

UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${existed_field}}' || jsonb_build_object('${new_field}', jsonb->'${existed_field}');

^*Here and below placeholder ${name} should be changed to the actual name in query.

Example of usage

1) Select one existing record from DB

SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "d50a347c-ab10-413e-a1c5-3c28e5e3d876",
    "name": null,
    "notes": [],
    "vendor": "f0fb6130-cdf1-11e8-a8d5-f2801f1b9fd1",
    "metadata": {
        "createdDate": "2019-05-31T17:19:39.602",
        "updatedDate": "2019-05-31T17:19:39.855+00",
        "createdByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc",
        "updatedByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc"
    },
    "poNumber": "10375",
    "orderType": "One-Time",
    "dateOrdered": "2019-05-31T17:19:39.752+0000",
    "workflowStatus": "Open"
}

2) Rename field 'poNumber' by executing query from Snippet 1.1

UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{poNumber}' || jsonb_build_object('poNumberChanged', jsonb->'poNumber');

3) Select one existing record from DB to be sure that field "poNumber" is changed to "poNumberChanged"

SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "8e3f16fa-d9bb-4117-8dec-55e0b58ebc79",
    "name": null,
    "notes": [],
    "vendor": "f0fb6130-cdf1-11e8-a8d5-f2801f1b9fd1",
    "metadata": {
        "createdDate": "2019-05-31T17:19:43.793",
        "updatedDate": "2019-05-31T17:19:44.213+00",
        "createdByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc",
        "updatedByUserId": "030d554a-2c41-5a9a-85b9-afafaff88abc"
    },
    "orderType": "One-Time",
    "workflowStatus": "Open",
    "poNumberChanged": "23421"
}

1.2 Field has been removed from a schema

RMB supports field removing by adding deleteFields element in the table schema:

"deleteFields": [{
		"fieldName": "${existed_field}"
	}
]

This corresponds to the following SQL query:

Snippet 1.2 - Field removing

UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${existed_field}}';

Example of usage

1) Select one existing record from DB

SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
    "notes": [
        "Cataloging record service"
    ],
    "owner": "Monographs",
    "vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "38434",
    "orderType": "Ongoing",
    "reEncumber": false,
    "workflowStatus": "Pending"
}

2) Remove field by executing query from Snippet 1.2 for field workflowStatus

UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{workflowStatus}';

3) Select one existing record from DB to be sure that workflowStatus is removed

SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
    "notes": [
        "Cataloging record service"
    ],
    "owner": "Monographs",
    "vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "38434",
    "orderType": "Ongoing",
    "reEncumber": false
}

1.3 Field becomes required

Required field means that it should be presented in DB. Namely, we need to check that required field exists. If yes - leave it as is, if no - add required field to JSONB with any default indicator that this value should be presented. RMB doesn't support this scenario but this can be provided by the following SQL queries:

Snippet 1.3 - Required field

UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${required_field}}' || jsonb_build_object('${required_field}', jsonb->'${required_field}');
UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb_set(jsonb, '{${required_field}}', '"REQUIRED"') WHERE jsonb->'${required_field}' = 'null';

Example of usage

1) Select one existing record from DB

SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
    "notes": [
        "Cataloging record service"
    ],
    "owner": "Monographs",
    "owners": "required",
    "vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "38434",
    "orderType": "Ongoing",
    "reEncumber": false
}

2) Add required field required_field by executing query from Snippet 1.3:

UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{required_field}' || jsonb_build_object('required_field', jsonb->'required_field');
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb_set(jsonb, '{required_field}', '"required"') WHERE jsonb->'required_field' = 'null';

3) Select one existing record from DB again to be sure that required_field is added

SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
    "notes": [
        "Cataloging record service"
    ],
    "owner": "Monographs",
    "owners": "required",
    "vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "38434",
    "orderType": "Ongoing",
    "reEncumber": false,
    "required_field": "required"
}

4) Execute query from Snippet 1.3 for existed field orderType to be sure that there are no any changes in JSONB record

UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb #- '{orderType}' || jsonb_build_object('orderType', jsonb->'orderType');
UPDATE diku_mod_orders_storage.purchase_order SET jsonb = jsonb_set(jsonb, '{orderType}', '"required"') WHERE jsonb->'orderType' = 'null';
SELECT diku_mod_orders_storage.purchase_order.jsonb FROM diku_mod_orders_storage.purchase_order LIMIT 1;

{
    "id": "c27e60f9-6361-44c1-976e-0c4821a33a7d",
    "notes": [
        "Cataloging record service"
    ],
    "owner": "Monographs",
    "owners": "required",
    "vendor": "e0fb5df2-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "38434",
    "orderType": "Ongoing",
    "reEncumber": false,
    "required_field": "required"
}

1.4 Field type has been changed

1.4.1 From free-form String to enum

Since the representation for a string value in the database is identical to the representation of the enumerator element, it suffices to do the same operations as for the string. That is, if the string value does not differ from the enum element, then leave the corresponding entry unchanged, if we need to change the string value to enum value then it is enough to update the value of this field with the following SQL query:

Snippet 1.4 - Transform from free-form String to enum

UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb_set(jsonb, '{${required_field}}', '"${new_value}"');

1.4.2 From single value to array or vice-versa

This transformation can be provided for single json value ↔ single element array

Snippet 1.5 - Transform single value to array

UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${changed_field}}' || jsonb_build_object('${changing_field}', json_build_array(jsonb -> '${changing_field}'));

Snippet 1.6 - Transform single-element array to json value

UPDATE ${tenant}_${module}.${table} SET jsonb = jsonb #- '{${changed_field}}' || jsonb_build_object('${changing_field}', (jsonb -> '${changing_field}')::json ->> 0);

Example of usage

1) Select one existing po_line record from DB

SELECT * FROM diku_mod_orders_storage.po_line LIMIT 1;

{
    "id": "774608cf-6e7f-498a-b0c6-3bd38d8b8f9e",
    "cost": {
        "currency": "USD",
        "discount": 47.4,
        "discountType": "percentage",
        "listUnitPrice": 68,
        "additionalCost": 0,
        "quantityPhysical": 1,
        "quantityElectronic": 0,
        "poLineEstimatedPrice": 35.768,
        "listUnitPriceElectronic": 0
    },
    "rush": false,
    "tags": [],
    "donor": "",
    "title": "Bridget Jones's Baby: the diaries",
    "alerts": [],
    "claims": [
        {
            "grace": 90,
            "claimed": false
        }
    ],
    "source": {
        "code": "",
        "description": "Manually entered"
    },
    "details": {
        "productIds": [
            {
                "productId": "10407849",
                "productIdType": "8261054f-be78-422d-bd51-4ed9f33c3422"
            }
        ],
        "receivingNote": "",
        "subscriptionInterval": 0
    },
    "edition": "First edition",
    "physical": {
        "volumes": [
            "vol. 1"
        ],
        "receiptDue": "2018-09-29T00:00:00.000+0000",
        "materialType": "d9acad2f-2aac-4b48-9097-e6ab85906b25",
        "createInventory": "Instance",
        "materialSupplier": "60fb4bfa-cdf1-11e8-a8d5-f2801f1b9fd1"
    },
    "selector": "Stevens",
    "locations": [
        {
            "quantity": 1,
            "locationId": "fcd64ce1-6995-48f0-840e-89ffa2288371",
            "quantityPhysical": 1,
            "quantityElectronic": 0
        }
    ],
    "publisher": "Alfred A. Knopf",
    "requester": "",
    "collection": false,
    "instanceId": "7fbd5d84-62d1-44c6-9c45-6cb173998bbd",
    "agreementId": "7324c8eb-5e81-4109-812b-411c26ef09f2",
    "description": "",
    "orderFormat": "Physical Resource",
    "checkinItems": false,
    "contributors": [
        {
            "contributor": "Fielding, Helen",
            "contributorType": "2b94c631-fca9-4892-a730-03ee529ffe2a"
        }
    ],
    "poLineNumber": "268758-02",
    "vendorDetail": {
        "instructions": "",
        "vendorAccount": "",
        "noteFromVendor": ""
    },
    "paymentStatus": "Awaiting Payment",
    "receiptStatus": "Awaiting Receipt",
    "reportingCodes": [],
    "publicationDate": "2016",
    "purchaseOrderId": "efff3ffe-5469-1f8f-aa3a-7afc933f9eff",
    "fundDistribution": [
        {
            "code": "EUROHIST",
            "fundId": "e9285a1c-1dfc-4380-868c-e74073003f43",
            "percentage": 100,
            "encumbrance": "eb506834-6c70-4239-8d1a-6414a5b08003"
        }
    ],
    "acquisitionMethod": "Purchase",
    "poLineDescription": "",
    "cancellationRestriction": false,
    "cancellationRestrictionNote": ""
}

2) Transform field 'title' of po_line to single-element array by executing query from Snippet 1.5 and verify that field 'title' has array type:

UPDATE diku_mod_orders_storage.po_line SET jsonb = jsonb #- '{title}' || jsonb_build_object('title', json_build_array(jsonb -> 'title'));
SELECT * FROM diku_mod_orders_storage.po_line LIMIT 1;

{
    "id": "774608cf-6e7f-498a-b0c6-3bd38d8b8f9e",
    "cost": {
        "currency": "USD",
        "discount": 47.4,
        "discountType": "percentage",
        "listUnitPrice": 68,
        "additionalCost": 0,
        "quantityPhysical": 1,
        "quantityElectronic": 0,
        "poLineEstimatedPrice": 35.768,
        "listUnitPriceElectronic": 0
    },
    "rush": false,
    "tags": [],
    "donor": "",
    "title": [
        "Bridget Jones's Baby: the diaries"
    ],
    "alerts": [],
    "claims": [
        {
            "grace": 90,
            "claimed": false
        }
    ],
    "source": {
        "code": "",
        "description": "Manually entered"
    },
    "details": {
        "productIds": [
            {
                "productId": "10407849",
                "productIdType": "8261054f-be78-422d-bd51-4ed9f33c3422"
            }
        ],
        "receivingNote": "",
        "subscriptionInterval": 0
    },
    "edition": "First edition",
    "physical": {
        "volumes": [
            "vol. 1"
        ],
        "receiptDue": "2018-09-29T00:00:00.000+0000",
        "materialType": "d9acad2f-2aac-4b48-9097-e6ab85906b25",
        "createInventory": "Instance",
        "materialSupplier": "60fb4bfa-cdf1-11e8-a8d5-f2801f1b9fd1"
    },
    "selector": "Stevens",
    "locations": [
        {
            "quantity": 1,
            "locationId": "fcd64ce1-6995-48f0-840e-89ffa2288371",
            "quantityPhysical": 1,
            "quantityElectronic": 0
        }
    ],
    "publisher": "Alfred A. Knopf",
    "requester": "",
    "collection": false,
    "instanceId": "7fbd5d84-62d1-44c6-9c45-6cb173998bbd",
    "agreementId": "7324c8eb-5e81-4109-812b-411c26ef09f2",
    "description": "",
    "orderFormat": "Physical Resource",
    "checkinItems": false,
    "contributors": [
        {
            "contributor": "Fielding, Helen",
            "contributorType": "2b94c631-fca9-4892-a730-03ee529ffe2a"
        }
    ],
    "poLineNumber": "268758-02",
    "vendorDetail": {
        "instructions": "",
        "vendorAccount": "",
        "noteFromVendor": ""
    },
    "paymentStatus": "Awaiting Payment",
    "receiptStatus": "Awaiting Receipt",
    "reportingCodes": [],
    "publicationDate": "2016",
    "purchaseOrderId": "efff3ffe-5469-1f8f-aa3a-7afc933f9eff",
    "fundDistribution": [
        {
            "code": "EUROHIST",
            "fundId": "e9285a1c-1dfc-4380-868c-e74073003f43",
            "percentage": 100,
            "encumbrance": "eb506834-6c70-4239-8d1a-6414a5b08003"
        }
    ],
    "acquisitionMethod": "Purchase",
    "poLineDescription": "",
    "cancellationRestriction": false,
    "cancellationRestrictionNote": ""
}

3) Transform single-element array to json field 'title' by executing query from Snippet 1.6 and verify that array was transformed to json field:

UPDATE diku_mod_orders_storage.po_line SET jsonb = jsonb #- '{title}' || jsonb_build_object('title', (jsonb -> 'title')::json->>0);
SELECT * FROM diku_mod_orders_storage.po_line LIMIT 1;

{
    "id": "774608cf-6e7f-498a-b0c6-3bd38d8b8f9e",
    "cost": {
        "currency": "USD",
        "discount": 47.4,
        "discountType": "percentage",
        "listUnitPrice": 68,
        "additionalCost": 0,
        "quantityPhysical": 1,
        "quantityElectronic": 0,
        "poLineEstimatedPrice": 35.768,
        "listUnitPriceElectronic": 0
    },
    "rush": false,
    "tags": [],
    "donor": "",
    "title": "Bridget Jones's Baby: the diaries",
    "alerts": [],
    "claims": [
        {
            "grace": 90,
            "claimed": false
        }
    ],
    "source": {
        "code": "",
        "description": "Manually entered"
    },
    "details": {
        "productIds": [
            {
                "productId": "10407849",
                "productIdType": "8261054f-be78-422d-bd51-4ed9f33c3422"
            }
        ],
        "receivingNote": "",
        "subscriptionInterval": 0
    },
    "edition": "First edition",
    "physical": {
        "volumes": [
            "vol. 1"
        ],
        "receiptDue": "2018-09-29T00:00:00.000+0000",
        "materialType": "d9acad2f-2aac-4b48-9097-e6ab85906b25",
        "createInventory": "Instance",
        "materialSupplier": "60fb4bfa-cdf1-11e8-a8d5-f2801f1b9fd1"
    },
    "selector": "Stevens",
    "locations": [
        {
            "quantity": 1,
            "locationId": "fcd64ce1-6995-48f0-840e-89ffa2288371",
            "quantityPhysical": 1,
            "quantityElectronic": 0
        }
    ],
    "publisher": "Alfred A. Knopf",
    "requester": "",
    "collection": false,
    "instanceId": "7fbd5d84-62d1-44c6-9c45-6cb173998bbd",
    "agreementId": "7324c8eb-5e81-4109-812b-411c26ef09f2",
    "description": "",
    "orderFormat": "Physical Resource",
    "checkinItems": false,
    "contributors": [
        {
            "contributor": "Fielding, Helen",
            "contributorType": "2b94c631-fca9-4892-a730-03ee529ffe2a"
        }
    ],
    "poLineNumber": "268758-02",
    "vendorDetail": {
        "instructions": "",
        "vendorAccount": "",
        "noteFromVendor": ""
    },
    "paymentStatus": "Awaiting Payment",
    "receiptStatus": "Awaiting Receipt",
    "reportingCodes": [],
    "publicationDate": "2016",
    "purchaseOrderId": "efff3ffe-5469-1f8f-aa3a-7afc933f9eff",
    "fundDistribution": [
        {
            "code": "EUROHIST",
            "fundId": "e9285a1c-1dfc-4380-868c-e74073003f43",
            "percentage": 100,
            "encumbrance": "eb506834-6c70-4239-8d1a-6414a5b08003"
        }
    ],
    "acquisitionMethod": "Purchase",
    "poLineDescription": "",
    "cancellationRestriction": false,
    "cancellationRestrictionNote": ""
}

1.4.3 An enum list has changed (entry has been removed, or renamed)

Since the enumerator has the same representation DB as a string value, the queries from 1.1 and 1.2 are relevant to it. Enumerator element can be removed by analogy with the string representation with using query from Snippet 1.2. And the renaming of the enumerator element can be provided by using the query from Snippet 1.4.

1.5 A unique index has been created on a field

It might have that field which should become unique is repeated. An error will occur when trying to create a unique index for this field. That is why it is necessary to ensure uniqueness of this field.

Snippet 1.7 - Rename duplicates

UPDATE ${tenant}_${module}.${table}
SET jsonb = jsonb || jsonb_build_object(${required_field}, jsonb ->> '${required_field}' || ${unique_suffix})
WHERE jsonb ->> '${required_field}' IN (SELECT jsonb->>'${required_field}' FROM ${tenant}_${module}.${table} GROUP BY jsonb ->> '${required_field}' HAVING COUNT(*) > 1);

2. Sequences

RMB doesn't support sequence manipulations. Sequence creation/deletion/modification can be done by customSnippet element in the schema or directly in DB by the following SQL queries:

Snippet 2.1 - Sequence creating

DROP SEQUENCE IF EXISTS ${tenant}_${module}.${sequence} CASCADE;

Snippet 2.2 - Sequence removing

CREATE SEQUENCE IF NOT EXISTS ${university}_${module}.${sequence} NO MAXVALUE START WITH ${start_value} CACHE 1 NO CYCLE

It is clear that sequence changing can be done by consequent creation new modified sequence after initial its deletion

Snippet 2.3 - Sequence changing

DROP SEQUENCE IF EXISTS ${university}_${module}.${sequence} CASCADE;
CREATE SEQUENCE IF NOT EXISTS ${university}_${module}.${sequence} NO MAXVALUE START WITH ${start_value} CACHE 1 NO CYCLE;

Example of usage

1) Retrieve sequence number

SELECT nextval('diku_mod_orders_storage.po_number');

2) Modify sequence (change start value) - delete sequence and create new one with the same name but the other characteristics, verify that start value is changed.

DROP SEQUENCE IF EXISTS diku_mod_orders_storage.po_number CASCADE;
CREATE SEQUENCE IF NOT EXISTS diku_mod_orders_storage.po_number NO MAXVALUE START WITH 22222 CACHE 1 NO CYCLE;
SELECT nextval('diku_mod_orders_storage.po_number');

3. Views

RMB supports view creation:

"views": [
    {
      "viewName": "view",
      "mode": "new",
      "joinType": "JOIN",
      "join": [
        {
          "table": {
            "tableName": "table_1",
            "joinOnField": "table_field_1"
          },
          "joinTable": {
            "tableName": "table_2",
            "joinOnField": "table_field_2",
            "jsonFieldAlias": "alias_jsonb"
          }
        }
      ]
    }
  ]

This corresponds to the following SQL snippet:

Snippet 3.1 - View creation

CREATE OR REPLACE VIEW diku_mod_orders_storage.view AS select
    table_1.id,
    table_1.jsonb as jsonb,
    table_2.jsonb as alias_jsonb  from
    diku_mod_orders_storage.table_1 table_1
    JOIN
    diku_mod_orders_storage.table_2 table_2
    on
    (table_1.jsonb->>'table_field_1')
    =
    (table_2.jsonb->>'table_field_2')
  ;

View can be deleted by execution of the following SQL query

Snippet 3.2 - View removing

DROP VIEW IF EXISTS ${tenant}_${module}.${view} CASCADE;

View changing can be provided as for sequences, i.e. by deletion of existing view and creation changed one with the same name.

Example of usage

1) Retrieve existing view

SELECT DISTINCT ON (lower(f_unaccent(metadata->>'poNumber'))) jsonb FROM diku_mod_orders_storage.orders_view LIMIT 1 OFFSET 0;

{
    "id": "589a6016-3463-49f6-8aa2-dc315d0665fd",
    "notes": [],
    "owner": "Acquisitions - Serials",
    "vendor": "14fb6608-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "101101",
    "orderType": "Ongoing",
    "reEncumber": false
}

2) Remove view

DROP VIEW IF EXISTS diku_mod_orders_storage.orders_view CASCADE;

3) Modify view (for example, change start value) - create new view with the same name but the other characteristics

CREATE OR REPLACE VIEW diku_mod_orders_storage.orders_view AS SELECT diku_mod_orders_storage.purchase_order.id AS id, diku_mod_orders_storage.purchase_order.jsonb AS jsonb, COALESCE(po_line.jsonb, '{}'::jsonb) || diku_mod_orders_storage.purchase_order.jsonb AS metadata FROM diku_mod_orders_storage.purchase_order LEFT JOIN diku_mod_orders_storage.po_line ON (po_line.jsonb -> 'purchaseOrderId'::text) = (purchase_order.jsonb -> 'id'::text);

4) Retrieve view and verify that it is changed.

SELECT DISTINCT ON (lower(f_unaccent(metadata->>'poNumber'))) jsonb FROM diku_mod_orders_storage.orders_view LIMIT 1 OFFSET 0;

{
    "id": "589a6016-3463-49f6-8aa2-dc315d0665fd",
    "notes": [],
    "owner": "Acquisitions - Serials",
    "vendor": "14fb6608-cdf1-11e8-a8d5-f2801f1b9fd1",
    "renewal": {
        "cycle": "6 Months",
        "interval": 182,
        "renewalDate": "2019-04-09T00:00:00.000+0000",
        "reviewPeriod": 30,
        "manualRenewal": true
    },
    "approved": true,
    "manualPo": true,
    "poNumber": "101101",
    "orderType": "Ongoing",
    "reEncumber": false
}

More details for PostgreSQL JSON functions can be founded here: https://www.postgresql.org/docs/10/functions-json.html.

Browser not supported