I have a column type CLOB that stores an array of objects containing the following structure:

{"id": number, "name": string}

Then suppose we have the following column of CLOB records:

1. [{"id": 21, "nombre": "fisica"}, {"id": 16, "nombre": "auditiva"}]
2. [{"id": 16, "nombre": "auditiva"}]
3. [{"id": 4, "nombre": "intelectual"}, {"id": 21, "nombre": "fisica"}]

and so several records that its array in the CLOB column contains at least one object with id and name; then suppose that I have the previous records, is it possible to sort that column by the name property of each first object of each record? example of expected result

1. [{"id": 16, "name": "auditiva"}]
2. [{"id": 21, "name": "fisica"}, {"id": 16, "name": "auditiva"}]
3. [{"id": 4, "name": "intelectual"}, {"id": 21, "name": "fisica"}]

CodePudding user response：

Use the JSON_VALUE function in the ORDER BY clause:

SELECT *
FROM   table_name 
ORDER BY JSON_VALUE(value, '$[0].nombre');

Which, for the sample data:

CREATE TABLE table_name (
  value CLOB CHECK (value IS JSON)
);

INSERT INTO table_name (value)
  SELECT '[{"id": 21, "nombre": "fisica"}, {"id": 16, "nombre": "auditiva"}]' FROM DUAL UNION ALL
  SELECT '[{"id": 16, "nombre": "auditiva"}]' FROM DUAL UNION ALL
  SELECT '[{"id": 4, "nombre": "intelectual"}, {"id": 21, "nombre": "fisica"}]' FROM DUAL;

Outputs:

fiddle