How do I: create ad-hoc relations?

It’s often useful to make a small inline relation, for example when exploring how a database will evaluate an expression, or for a small lookup table. This can be quite verbose in SQL.

PRQL offers two approaches — array literals, and a from_text transform.

Array literals

Because relations (aka a table) in PRQL are just arrays of tuples, they can be expressed with array and tuple syntax:

PRQL

1. from [
2. {a=5, b=false},
3. {a=6, b=true},
4. ]
5. filter b == true
6. select a

SQL

1. WITH table_0 AS (
2. SELECT
3. 5 AS a,
4. false AS b
5. UNION
6. ALL
7. SELECT
8. 6 AS a,
9. true AS b
10. )
11. SELECT
12. a
13. FROM
14. table_0
15. WHERE
16. b = true

PRQL

1. let my_artists = [
2. {artist="Miles Davis"},
3. {artist="Marvin Gaye"},
4. {artist="James Brown"},
5. ]

7. from artists
8. join my_artists (==artist)
9. join albums (==artist_id)
10. select {artists.artist_id, albums.title}

SQL

1. WITH table_0 AS (
2. SELECT
3. 'Miles Davis' AS artist
4. UNION
5. ALL
6. SELECT
7. 'Marvin Gaye' AS artist
8. UNION
9. ALL
10. SELECT
11. 'James Brown' AS artist
12. ),
13. my_artists AS (
14. SELECT
15. artist
16. FROM
17. table_0
18. )
19. SELECT
20. artists.artist_id,
21. albums.title
22. FROM
23. artists
24. JOIN my_artists ON artists.artist = my_artists.artist
25. JOIN albums ON artists.artist_id = albums.artist_id

from_text

from_text takes a string in a common format, and converts it to table. It accepts a few formats:

  • format:csv parses CSV (default),

  • format:json parses either:

  • an array of objects each of which represents a row, or

  • an object with fields columns & data, where columns take an array of column names and data takes an array of arrays.

PRQL

1. from_text """
2. a,b,c
3. 1,2,3
4. 4,5,6
5. """
6. derive {
7. d = b + c,
8. answer = 20 * 2 + 2,
9. }

SQL

1. WITH table_0 AS (
2. SELECT
3. '1' AS a,
4. '2' AS b,
5. '3' AS c
6. UNION
7. ALL
8. SELECT
9. '4' AS a,
10. '5' AS b,
11. '6' AS c
12. )
13. SELECT
14. a,
15. b,
16. c,
17. b + c AS d,
18. 20 * 2 + 2 AS answer
19. FROM
20. table_0

PRQL

1. from_text format:json """
2. [
3. {"a": 1, "m": "5"},
4. {"a": 4, "n": "6"}
5. ]
6. """

SQL

1. WITH table_0 AS (
2. SELECT
3. 1 AS a,
4. '5' AS m
5. UNION
6. ALL
7. SELECT
8. 4 AS a,
9. NULL AS m
10. )
11. SELECT
12. a,
13. m
14. FROM
15. table_0

PRQL

1. from_text format:json """
2. {
3. "columns": ["a", "b", "c"],
4. "data": [
5. [1, "x", false],
6. [4, "y", null]
7. ]
8. }
9. """

SQL

1. WITH table_0 AS (
2. SELECT
3. 1 AS a,
4. 'x' AS b,
5. false AS c
6. UNION
7. ALL
8. SELECT
9. 4 AS a,
10. 'y' AS b,
11. NULL AS c
12. )
13. SELECT
14. a,
15. b,
16. c
17. FROM
18. table_0

See also