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.

from_text """
a,b,c
1,2,3
4,5,6
"""
derive [
    d = b + c,
    answer = 20 * 2 + 2,
]

WITH table_0 AS (
  SELECT
    '1' AS a,
    '2' AS b,
    '3' AS c
  UNION
  ALL
  SELECT
    '4' AS a,
    '5' AS b,
    '6' AS c
)
SELECT
  a,
  b,
  c,
  b + c AS d,
  42 AS answer
FROM
  table_0 AS table_1

An example of adding a small lookup table:

let temp_format_lookup = from_text format:csv """
country_code,format
uk,C
us,F
lr,F
de,C
"""

from temperatures
join temp_format_lookup [==country_code]

WITH table_0 AS (
  SELECT
    'uk' AS country_code,
    'C' AS format
  UNION
  ALL
  SELECT
    'us' AS country_code,
    'F' AS format
  UNION
  ALL
  SELECT
    'lr' AS country_code,
    'F' AS format
  UNION
  ALL
  SELECT
    'de' AS country_code,
    'C' AS format
),
temp_format_lookup AS (
  SELECT
    country_code,
    format
  FROM
    table_0 AS table_1
)
SELECT
  temperatures.*,
  temp_format_lookup.country_code,
  temp_format_lookup.format
FROM
  temperatures
  JOIN temp_format_lookup ON temperatures.country_code = temp_format_lookup.country_code

And JSON:

let x = from_text format:json """{
    "columns": ["a", "b", "c"],
    "data": [
        [1, "x", false],
        [4, "y", null]
    ]
}"""

let y = from_text format:json """
    [
        {"a": 1, "m": "5"},
        {"a": 4, "n": "6"}
    ]
"""

from x | join y [==a]

WITH table_0 AS (
  SELECT
    1 AS a,
    'x' AS b,
    false AS c
  UNION
  ALL
  SELECT
    4 AS a,
    'y' AS b,
    NULL AS c
),
x AS (
  SELECT
    a,
    b,
    c
  FROM
    table_0 AS table_1
),
table_2 AS (
  SELECT
    1 AS a,
    '5' AS m
  UNION
  ALL
  SELECT
    4 AS a,
    NULL AS m
),
y AS (
  SELECT
    a,
    m
  FROM
    table_2 AS table_3
)
SELECT
  x.a,
  x.b,
  x.c,
  y.a,
  y.m
FROM
  x
  JOIN y ON x.a = y.a