SQL Arrays and JSON¶
Arrays¶
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¶
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¶
Index of value in array¶
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
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, ...)
- Use
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¶
????
Ordinality¶
Adds a new bigint
column
- starts with 1
Retain the original position of each element
Useful in aggregations
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¶
Last update:
2023-04-24