Search code examples
postgresqlscalaslick

Writing a rather obtuse JSON query using Slick

I am looking to translate an SQL query (Postgres) into Scala Slick code for use in my Play application.

The data looks something like this:

parent_id | json_column
----------+-----------------------------------------
          | [ {"id": "abcde-12345", "data": "..."}
2         | , {"id": "67890-fghij", "data": "..."}
          | , {"id": "klmno-00000", "data": "..."} ]

Here's my query in PostgreSQL:

SELECT * FROM table1
WHERE id IN (
  SELECT id
  FROM
    table1 t1,
    json_array_elements(t1.json_column) e,
    json_to_record(e.value) AS r("id" text, data text)
  WHERE
    "id" = 'abcde-12345'
    AND t1.parent_id = 2
);

This finds the results I need; any objects in t1 that include a "row" in the json_column array that has the id of "abcde-12345". The "parent_id" and "id" will be passed in to this query via query parameters (both Strings).

How would I write this query in Scala using Slick?

Solution

  • The easiest - maybe laziest? - way is probably to just use plain sql .. 

    sql"""[query]""".as[ (type1,type2..) ]

    using the $var notation for the variables.

    Otherwise you can use SimpleFunction to map the json calls, but I'm not quite sure how that works when they generate multiple results per row. Seems that might get complicated..