Chapter 11. NoSQL (Not Only SQL) Support

Index:

1. Declaration of NoSQL Types
2. NoSQL Functions
3. NoSQL sample

Declaration of Array Types

An array data type is named by appending square brackets ([]) to the data type name of the array elements, and all the elements are of the same data type. The array subscript numbers are written within square brackets. It uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].
DECLARE v_pay_by_month decimal(12,2)[12];
set v_pay_by_month[2]=123.45;
select v_pay_by_month[2] as feb;
select v_pay_by_month;

Array Functions

1. CAST(expression AS array): converts expression to array. For instance, cast('{20000, 25000, 25000, 25000}' as array).
   
2. CONVERT(expression, data_type): converts expression to another data type (json and jsonb). For instance, convert('{{"breakfast", "consulting"}, {"meeting", "lunch"}}', array).
   
3. to_array(expression): Returns the value as array. For instance, to_array('{{"breakfast", "consulting"}, {"meeting", "lunch"}}').
   
4. array_assign(anyArrayElement,expression): Returns an array with supplied value. For instance, array_assign(to_array('{{"breakfast", "consulting"}, {"meeting", "lunch"}}')[2],to_array('{"lunch", "meeting"}')).
   
5. array_ndims(anyArrayElement): Returns the number of dimensions of the array.
   
6. SPLIT(string1, string2): split string1 according to delimiter string2, and return an String[] object (Types.ARRAY). Special SPLIT(expression,'') will return strings which contains only Letter and Digit. Special SPLIT(expression,null) will split string into length=1 strings, which is only Letter or Digit.
7. CINT(arrayExpr): converts an array object to a int[] object (Types.ARRAY).
8. CLNG(arrayExpr): converts an array object to a long[] object (Types.ARRAY).
9. CDBL(arrayExpr): converts an array object to a double[] object (Types.ARRAY).
10. VAL(arrayExpr): converts an array object to an Object[] object (Types.ARRAY) which contains numeric values.

Array sample

SELECT cast('{20000, 25000, 25000, 25000}' as array);
SELECT convert('{{"breakfast", "consulting"}, {"meeting", "lunch"}}', array);
select to_array('{{"meeting", "lunch"}, {"training", "presentation"}}');

select to_array('{{"meeting", "lunch"}, {"training", "presentation"}}')[1][2];

select array_assign(to_array('{{"breakfast", "consulting"}, {"meeting", "lunch"}}')[2],to_array('{"lunch", "meeting"}'));