How to process json data correctly in dremio?

1

Hello!

I’m new to dremio and can’t figure out how to work with json correctly.
For example - I have data in S3 (parquet) in dremio they are displayed as text.
Accordingly, I need to reformat to json(struct) - but how to do it correctly?

For example, if I do something like this, it takes a long time (I have about 3 million lines).(And it’s not always fulfilled to the end)

SELECT a, b, c, d convert_from(nested_0.“e”, ‘JSON’) AS “e_json”
FROM (
select * from my_table
) nested_0

Is there any better way to do it?

2

@skantor If the file format is Parquet and you have array of structs, you can directly flatten the array and extract the element from the resulting struct. No need to convert to JSON

SELECT fragmentProfile, nested_0.fragmentProfile.majorFragmentId AS majorFragmentId
FROM (
  SELECT FLATTEN("profile_attempt_0"."fragmentProfile") AS fragmentProfile
  FROM "@dremio".profile_attempt_0 AS profile_attempt_0
) nested_0;