From text

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

PRQL uses from_text for this.

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. 42 AS answer
19. FROM
20. table_0 AS table_1

An example of adding a small lookup table:

PRQL

1. let temp_format_lookup = from_text format:csv """
2. country_code,format
3. uk,C
4. us,F
5. lr,F
6. de,C
7. """

9. from temperatures
10. join temp_format_lookup [==country_code]

SQL

1. WITH table_0 AS (
2. SELECT
3. 'uk' AS country_code,
4. 'C' AS format
5. UNION
6. ALL
7. SELECT
8. 'us' AS country_code,
9. 'F' AS format
10. UNION
11. ALL
12. SELECT
13. 'lr' AS country_code,
14. 'F' AS format
15. UNION
16. ALL
17. SELECT
18. 'de' AS country_code,
19. 'C' AS format
20. ),
21. temp_format_lookup AS (
22. SELECT
23. country_code,
24. format
25. FROM
26. table_0 AS table_1
27. )
28. SELECT
29. temperatures.*,
30. temp_format_lookup.country_code,
31. temp_format_lookup.format
32. FROM
33. temperatures
34. JOIN temp_format_lookup ON temperatures.country_code = temp_format_lookup.country_code

And JSON:

PRQL

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

9. let y = from_text format:json """
10. [
11. {"a": 1, "m": "5"},
12. {"a": 4, "n": "6"}
13. ]
14. """

16. from x | join y [==a]

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. x AS (
14. SELECT
15. a,
16. b,
17. c
18. FROM
19. table_0 AS table_1
20. ),
21. table_2 AS (
22. SELECT
23. 1 AS a,
24. '5' AS m
25. UNION
26. ALL
27. SELECT
28. 4 AS a,
29. NULL AS m
30. ),
31. y AS (
32. SELECT
33. a,
34. m
35. FROM
36. table_2 AS table_3
37. )
38. SELECT
39. x.a,
40. x.b,
41. x.c,
42. y.a,
43. y.m
44. FROM
45. x
46. JOIN y ON x.a = y.a