Unpacking nested structures

CrateDB SQL
1

Hi everyone,

I use crate to store data collected with Telegraf. And while I got the hang of accessing the nested structures like so …

select timestamp, name, 
fields['U_L1_L2'] as U_L1_L2, 
fields['U_L2_L3'] as U_L2_L3,
fields['U_L3_L1'] as U_L3_L1,
from E2 where name = 'sentron';

I was wondering if there was an automated way to unpack all objects in a query. There are ca. 30 columns so it is definitely possible to write the query by hand … just curious if there is a better way.

Cheers,
Frank

2

Hi @inviridi

Wondering why you wan’t to flatten :thinking: ?

If you just want all the columns without the brackets, this might work for you:

SELECT
  string_agg(column_name || ' AS ' || regexp_replace(column_name,'(.*\[\'')(.*)(\''\])', '$2'),' , ') AS flattend_columns
FROM
  information_schema.columns
WHERE
  table_name = 'satellites'
  AND table_schema = 'doc'
  AND column_name LIKE '%[%]';

Not the nicest, but maybe ok to start from :grimacing:

3

Totally valid question and it should have been mentioned. Reason: I read the table into a pandas dataframe like this…

pd.read_sql( "SELECT * from E2;", f'crate://localhost:4200')

and then unpack the json strings with:

fields = pd.json_normalize(df["fields"])
tags = pd.json_normalize(df["tags"])
df = pd.concat([df[["timestamp"]], fields, tags], axis=1)

Since my columns are rather fixed manually defining them in SQL is an option. But it feels almost as unelegant as normalizing columns in pandas.

Your proposed suggestion is very impressive but returns an empty dataframe, unfortunately.

1 Like
4

It was really only a solution to not manually type out all the column names :wink:

Afaik there is no real way right now to “flatten” the structure. The reality also is, that there is little difference between a column and an object property in CrateDB.

You could create a VIEW on the table E2 in CrateDB (again you would have to manually define it) and use it within python/pandas, if this is more convenient.

1 Like
5

I like your point of VIEW, @proddata! :slight_smile:

It is a practical and – most importantly – reusable solution. Thank you very much! :+1:

2 Likes