SQL Arrays and JSON

Arrays

SELECT
ARRAY
  [COL1, COL2]
FROM table

JSON_BUILD_ARRAY()

heterogeneous typed JSON array

> SELECT ARRAY['1', '2', 3, '4']
[1,2,3,4] -- converted to int

> SELECT JSON_BUILD_ARRAY('1', '2', 3, '4')
['1', '2', 3, '4']  -- keeps data types

How UNNEST works

SELECT
UNNEST(ARRAY\[1,2,3\]) as num1
UNNEST(ARRAY\[4,5\]) as num2
1 as one

Using UNNEST

SELECT
UNNEST(ARRAY[
  quota1_building_block_id,
    quota2_building_block_id
    ]) AS rule_block_id,
    *
FROM table_name

ARRAY vs ARbRAY_AGG

When there's a GROUP BY, aggregate into an array

id	forma_id	accessor_id
1	user's ID	manager's manager's ID
2	3	4

WITH data(category, subcategory, amount) AS (
  VALUES ('Quota 1', 'BCR', 123),
         ('Quota 1', 'ICR', 456),
         ('BT', 'BT', 789)
)
SELECT ARRAY_AGG(subcategory)
FROM data
GROUP BY category;

--| array_agg      |
--|----------------|
--| ['BT']         |
--| ['BCR', 'ICR'] |

you can also ARRAY_AGG(table_name) - turns the table into an array of arrays

WITH data(category, subcategory, amount) AS (
  VALUES ('Quota 1', 'BCR', 123),
         ('Quota 1', 'ICR', 456),
         ('BT', 'BT', 789)
)
SELECT ARRAY_AGG(data)
FROM data

[!output of ARRAY_AGG(table_name)]- |array_agg | +---------------------------------------------------------------+ |{"(\"Quota 1\",BCR,123)","(\"Quota 1\",ICR,456)","(BT,BT,789)"}|

JSONB to array

> TRANSLATE('["ICR"]'::jsonb::text, '[]', '{}')::TEXT[]

{'ICR'}

Index of value in array

ARRAY_POSITION('{"ICR", "ACR"}'::TEXT[], 'ICR')

JSON

[JSON vs JSONB in Postgres](https://stackoverflow.com/a/39637548/8479344](https://stackoverflow.com/a/39637548/8479344)

• jsonb
  • mostly use this
  • has an actual data structure, has actual operations, concatenation, …
• json stores it as plain text with whitespace
  • can't do those operations
  • use if you're processing logs and use it more like an audit trail

-> vs ->>

• -> returns the result as JSON
  • great to get nested objects
• ->> returns the result as text

Column name: info

{
    "customer": "Lily Bush",
    "items": {
        "product": "Diaper",
        "qty": 24
    }
}

SELECT info -> 'items' ->> 'product'
-- Diaper as text

JSON_AGG

like ARRAY_AGG,

WITH my_table(str_key, int_key, null_key) AS (
  VALUES ('commission', 123, NULL),
         ('adjustment', 456, NULL),
         ('spiff', 789, NULL)
)
SELECT json_agg(my_table) AS col_name FROM my_table;

--+-----------------------------------------------------------+
--| col_name                                                  |
--|-----------------------------------------------------------|
--| [{"str_key":"commission","int_key":123,"null_key":null},  |
--|  {"str_key":"adjustment","int_key":456,"null_key":null},  |
--|  {"str_key":"spiff","int_key":789,"null_key":null}]       |
--+-----------------------------------------------------------+

JSONB_OBJECT

• Takes in two string arrays (they must be strings) of keys and values and zips them together
• need more than just strings?
  • Use json_build_object(key1, value1, key2, value2, ...)

WITH data(category, subcategory, amount) AS (
  VALUES ('Quota 1', 'BCR', 123),
         ('Quota 1', 'ICR', 456),
         ('BT', 'BT', 789)
)
SELECT
  category,
  ARRAY_AGG(subcategory),
  ARRAY_AGG((amount)::TEXT),
  JSONB_OBJECT(ARRAY_AGG(subcategory), ARRAY_AGG((amount)::TEXT)) AS breakdown
FROM data
GROUP BY category
ORDER BY ARRAY_POSITION(ARRAY['Quota 1', 'Quota 2', 'BT', 'KSO'], category)

JSON to String

SELECT jsoncol #>> '{}'
FROM mytable;

????

Ordinality

Adds a new bigint column - starts with 1

Retain the original position of each element

Useful in aggregations

Ordinality video

SELECT * FROM UNNEST(string_to_array('here is a string', ' '))

+--------+
| unnest |
|--------|
| here   |
| is     |
| a      |
| string |
+--------+

Simple example of using ORDINALITY

SELECT * FROM UNNEST(string_to_array('here is a string', ' '))
WITH ORDINALITY AS table_name(word, item_position)

+--------+------------+
| word   | item_position |
|--------+------------|
| here   | 1          |
| is     | 2          |
| a      | 3          |
| string | 4          |
+--------+------------+

SELECT * FROM UNNEST(string_to_array('here is a string', ' '))
WITH ORDINALITY AS table_name(word, item_position)
ORDER BY table_name.item_position DESC

+--------+------------+
| word   | item_position |
|--------+------------|
| string | 4          |
| a      | 3          |
| is     | 2          |
| here   | 1          |
+--------+------------+

Real life example of using Ordinality

SELECT UNNEST(ARRAY[1,2,3,4,5]);
UNION ALL
SELECT UNNEST(ARRAY[-1, -2, -3, -4, -5])

---

Last update: 2023-04-24