https://prefect.io logo
Title
m

Mike Grabbe

11/16/2022, 8:52 PM
I noticed an odd behavior of the JSON block in Prefect 2. I have this block that keeps track of reserved keywords for Snowflake. It's a dictionary with a few dozen key:value pairs. I create the block with the keys sorted alphabetically, but when I view the block in Prefect Cloud, the keys are sorted by length of key, and then alphabetically. This obviously doesn't affect functionality, but this is a weird sort order and it's very not easy to scan through.
1
Here's what it looks like:
And the original JSON if you want to try it yourself:
{
  "ACCOUNT": "Cannot be used as an identifier in a SHOW command (e.g. 'SHOW … IN <identifier>').",
  "ANY": "Reserved by ANSI.",
  "AS": "Reserved by ANSI.",
  "CAST": "Cannot be used as column reference in a scalar expression.",
  "CONNECTION": "Cannot be used as an identifier in a SHOW command (e.g. 'SHOW … IN <identifier>').",
  "CURRENT": "Reserved by ANSI.",
  "CURRENT_DATE": "Cannot be used as column name (reserved by ANSI).",
  "CURRENT_USER": "Cannot be used as column name (reserved by ANSI).",
  "DROP": "Reserved by ANSI.",
  "ELSE": "Reserved by ANSI.",
  "EXISTS": "Reserved by ANSI.",
  "FULL": "Cannot be used as table name or alias in a FROM clause.",
  "GRANT": "Reserved by ANSI.",
  "GROUP": "Reserved by ANSI.",
  "GSCLUSTER": "Cannot be used as an identifier in a SHOW command (e.g. 'SHOW … IN <identifier>').",
  "HAVING": "Reserved by ANSI.",
  "ILIKE": "Reserved by Snowflake.",
  "INTERSECT": "Reserved by ANSI.",
  "INTO": "Reserved by ANSI.",
  "LATERAL": "Cannot be used as table name or alias in a FROM clause.",
  "LEFT": "Cannot be used as table name or alias in a FROM clause.",
  "LIKE": "Reserved by ANSI.",
  "LOCALTIME": "Cannot be used as column name (reserved by ANSI).",
  "LOCALTIMESTAMP": "Cannot be used as column name (reserved by ANSI).",
  "NULL": "Reserved by ANSI.",
  "OF": "Reserved by ANSI.",
  "ORGANIZATION": "Cannot be used as an identifier in a SHOW command (e.g. 'SHOW … IN <identifier>').",
  "QUALIFY": "Reserved by Snowflake.",
  "REGEXP": "Reserved by Snowflake.",
  "ROW": "Reserved by ANSI.",
  "ROWS": "Reserved by ANSI.",
  "SAMPLE": "Reserved by ANSI.",
  "SOME": "Reserved by Snowflake.",
  "START": "Reserved by ANSI.",
  "TABLE": "Reserved by ANSI.",
  "TABLESAMPLE": "Reserved by ANSI.",
  "THEN": "Reserved by ANSI.",
  "UNION": "Reserved by ANSI.",
  "USING": "Cannot be used as table name or alias in a FROM clause.",
  "WHERE": "Reserved by ANSI.",
  "WITH": "Reserved by ANSI."
}
j

Jeff Hale

11/19/2022, 4:51 PM
Hi Mike! I raised this with the team and they explained that because the data is stored in jsonb format, it is unordered. It is interesting that key length is how the data gets returned from the db. We don’t currently do an alphabetical sort in the UI, because the JSON data might not get returned from the block in your code in the order that it would show in the UI. It’s best to just think of the JSON block as returning unordered data. If you would like it to be alphabetically ordered in the UI, please feel free to open a feature enhancement issue to that effect. If you want to make sure the data is ordered you can wrap it as an array like this, which is still valid JSON:
[{"foo": "bar"}, { "baz": "bat" }]
. Screenshot below of how the block looks. Takes a few more characters.