jsonb sintax is broken

[sezaru]

Using SQL breaks jsonb syntax like -> or ->>.

Here is an example:

defmodule Bla do
  use SQL, adapter: SQL.Adapters.Postgres

  def migrate_from_query do
    sql = ~SQL"""
    update
      payments.products
    set
      export_credits = (metadata -> 'value' ->> 'export')::bigint,
      skip_trace_credits = (metadata -> 'value' ->> 'skip_trace')::bigint
    """

    {query, []} = to_sql(sql)

    query
  end
end

This will generated the following query:

update payments.products set export_credits = (metadata - > 'value' - > > 'export') :: bigint, skip_trace_credits = (metadata - > 'value' - > > 'skip_trace') :: bigint

As you can see, it changed -> to - > and ->> to - > >.

[Schultzer]

Yup, I’ve not added json operators yet. I’ll get something up today or tomorrow.

[Schultzer]

Hey, I pushed a refactoring of the lexer and parser to main, which beside improves compilation and runtime performance also fixes your issue. Please let me know if you're experiancing any other issues.

[sezaru]

Is this in 0.3?

I just tried it and it gave me this error:

sql = """
    update
      payments.products
    set
      metadata = json_build_object(
        'type', 'credit',
        'value', json_build_object(
          'export', export_credits,
          'skip_trace', skip_trace_credits
        )
      )
    """

    {query, []} = to_sql(sql)

    query

** (KeyError) key :string not found in: "update\n  payments.products\nset\n  metadata = json_build_object(\n    'type', 'credit',\n    'value', json_build_object(\n      'export', export_credits,\n      'skip_trace', skip_trace_credits\n    )\n  )\n"

If you are using the dot syntax, such as map.field, make sure the left-hand side of the dot is a map
    (sql 0.3.0) lib/sql.ex:45: SQL.to_sql/1
    iex:53: Core.Repo.Migrations.AddMetadataFieldToProducts.migrate_to_query/0
    iex:2: (file)

[Schultzer]

Is this in 0.3?

I just tried it and it gave me this error:

sql = """
update
payments.products
set
metadata = json_build_object(
'type', 'credit',
'value', json_build_object(
'export', export_credits,
'skip_trace', skip_trace_credits
)
)
"""

{query, []} = to_sql(sql)

query

** (KeyError) key :string not found in: "update\n  payments.products\nset\n  metadata = json_build_object(\n    'type', 'credit',\n    'value', json_build_object(\n      'export', export_credits,\n      'skip_trace', skip_trace_credits\n    )\n  )\n"

If you are using the dot syntax, such as map.field, make sure the left-hand side of the dot is a map
   (sql 0.3.0) lib/sql.ex:45: SQL.to_sql/1
   iex:53: Core.Repo.Migrations.AddMetadataFieldToProducts.migrate_to_query/0
   iex:2: (file)

You can’t pass a string to SQL.to_sql, you’ll need to use the sigil.

Although the error message is not that helpful.