hi all, I am trying to select all the rows of a table, using from_json. However I am getting an unknown type: arrayNode error. Anyone can help please?
I might be wrong here, but I don't think PostgreSQL has a from_json() function like SQL does. here are the docs for the json functions postgresql does provide.
I am not really using PostgreSQL, but rather Spark SQL using Databricks connection.
my bad, 
it even says 'databricks' for resourceType. this SO post has a slightly different schema string you could try:
i think the post assumes, in your case, CPMapping.data is an object and CPMapping.data.key_name is an array
select from_json({{ CPMapping.data }}, 'key_name array<struct<LegalEntity: string, ClearingBank: string, MarginFinancingAvailable: integer, ReportingCurrency: string>>')
if CPMapping.data is an array
select from_json({{ CPMapping }}, 'data array<struct<LegalEntity: string, ClearingBank: string, MarginFinancingAvailable: integer, ReportingCurrency: string>>')
thanks for your advice, but I tried both options but still doesn't work.
That CPMapping.data is already an array I believe, and it has the following structure if I hover my mouse on it:
Any idea why it is throwing that error?
When using table data I've noticed strange "it's an array but not really an array" things. You could try to use {{formatDataAsObject(CPMapping.data)}} and you'll get an object with each of the fields of the table as a usable array and then join for each field array.
You might also try making a transformer to use CPMapping.data and return a new array and then use that as the source of your from_json statement.
Hi @qwww
I haven't been able to reproduce this exact error yet, but I did test out this simplified example from the Databricks docs
hope this helps narrow things down!
This copy + pasted example works:
But then if I move '{"a":1, "b":0.8}' to query2 and pass it in dynamically, I need to add extra quotes around it:
