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

from [
  {a=5, b=false},
  {a=6, b=true},
]
filter b == true
select a

SQL

WITH table_0 AS (
  SELECT
    5 AS a,
    false AS b
  UNION
  ALL
  SELECT
    6 AS a,
    true AS b
)
SELECT
  a
FROM
  table_0
WHERE
  b = true

PRQL

let my_artists = [
  {artist="Miles Davis"},
  {artist="Marvin Gaye"},
  {artist="James Brown"},
]
from artists
join my_artists (==artist)
join albums (==artist_id)
select {artists.artist_id, albums.title}

SQL

WITH table_0 AS (
  SELECT
    'Miles Davis' AS artist
  UNION
  ALL
  SELECT
    'Marvin Gaye' AS artist
  UNION
  ALL
  SELECT
    'James Brown' AS artist
),
my_artists AS (
  SELECT
    artist
  FROM
    table_0
)
SELECT
  artists.artist_id,
  albums.title
FROM
  artists
  JOIN my_artists ON artists.artist = my_artists.artist
  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

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

SQL

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,
  20 * 2 + 2 AS answer
FROM
  table_0

PRQL

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

SQL

WITH table_0 AS (
  SELECT
    1 AS a,
    '5' AS m
  UNION
  ALL
  SELECT
    4 AS a,
    NULL AS m
)
SELECT
  a,
  m
FROM
  table_0

PRQL

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

SQL

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
)
SELECT
  a,
  b,
  c
FROM
  table_0

See also

• How do I: read files?