Library Inventory with Content Properties

Querying for your library content with all properties included as columns and having their values provides as comma separated lists is unfortunately a relatively complex SQL operation and in snowflake must be done in 2 steps.
The first step generates the query based on the available content property columns and the second step then executes that query.
Below you will find the SQL you can run for step 1 and an example of what that produces for step 2, then finally a sample of the result set.

Step 1 - SQL Generation Query

👍

Preface

This SQL is somewhat difficult to understand. If you simply want to execute it and not dive into the details, feel free to copy/paste the SQL, execute it, and proceed to step 2. If you want to dive into the details, continue reading.

This query is difficult to understand as it has many parts that use advanced concepts and reads in an illogical manner as the top part of the query is generating the list of columns that then gets injected into the lower part of the query.

The gist of what this query does is broken down as follows:
The props CTE gets the distinct list of content properties that exist and are used on content
The propString CTE then takes that list of properties and converts it to a formatted segment of a select statement that assumes the properties will be packed into an object-like structure where this select segment will be inserted.
The packedPropValues CTE takes the list of property values that exist for each document in your library and packs the values into a comma separated list, but still has rows for each property per document.
The packedProps CTE takes the above packedPropValues and further packs the values into objects where each content property is a key in the object and we now have 1 object per library content.
Now that there is 1 object per library content, the select statement which was generated by propString has what it needs to execute on and when run will produce the SQL string documented in step 2 below.

with props as (
    select distinct 
    cpa.contentpropertyname,
    replace(cpa.contentpropertyname, '"', '\\"') as jsonescaped, 
    replace(cpa.contentpropertyname, '"', '""') as sqlescaped
    from contentpropertyassignments cpa
    order by contentpropertyname
), propString as (  
    select
    listagg(concat('pp.d:"', props.jsonescaped, '"::string as "', props.sqlescaped,'"'),', ') as str
    from props    
)
  
select 
  concat('
      with packedPropValues as (
         select
         cp.LIBRARYCONTENTID,
         cp.CONTENTPROPERTYNAME,
         listagg(cp.CONTENTPROPERTYVALUE, '', '') within group(order by cp.CONTENTPROPERTYVALUE) as field_value_list
         from contentpropertyassignments cp
         group by cp.LIBRARYCONTENTID, cp.CONTENTPROPERTYNAME     
      ), packedProps as (
         select
         lc.ID as LIBRARYCONTENTID,
         objectagg(ppv.CONTENTPROPERTYNAME::variant, ppv.field_value_list::variant) as d
         from LIBRARYCONTENTS lc
         left join packedPropValues ppv on lc.ID = ppv.LIBRARYCONTENTID 
         group by  lc.ID      
      )
         select
         lc.CREATEDAT, lc.TYPE, lc.FORMAT, lc.ID, lc.ISDELETED, lc.ISPUBLISHED, lc.LATESTLIBRARYCONTENTVERSIONCREATEDAT, lc.LATESTLIBRARYCONTENTVERSIONID, lc.LATESTLIBRARYCONTENTVERSIONSIZE, lc.LIBRARYURL, lc.MODIFIEDAT, lc.NAME, lc.NEWSCENTERURL, lc.DOCCENTERURL, lc.OWNERID, lc.OWNERUSERNAME, lc.PREVIEWIMAGEID, lc.PREVIEWIMAGEURL, lc.PUBLISHEDVERSIONEXPIRESAT, lc.TEAMSITEID, lc.TEAMSITENAME, lc.THUMBNAILIMAGEID, lc.THUMBNAILIMAGEURL, lc.VERSION, lc.DESCRIPTION, lc.ISCHECKEDOUT, lc.SHORTID, lc.PARENTFOLDERLIBRARYCONTENTID, lc.LIBRARYPATH,    
      ',   
      '   ',(select top 1 str from propString), 
      '  FROM LIBRARYCONTENTS  lc
         inner join packedProps pp on pp.LIBRARYCONTENTID = lc.id 
      \;
      '
  )
;

Step 2 - Copy the generated query

Step 1 will generate SQL that looks something like below. The only difference will be that the list of content properties will be different for your tenant.
If you like a more direct approach better than relying on a 2-step process that generates SQL, you can take this sample below and manually modify it to have your list of content properties and simply run it as-needed to export your inventory with properties, but if your property names change or are added or removed, you must manually maintain this SQL.

with packedPropValues as (
   select
   cp.LIBRARYCONTENTID,
   cp.CONTENTPROPERTYNAME,
   listagg(cp.CONTENTPROPERTYVALUE, ', ') within group(order by cp.CONTENTPROPERTYVALUE) as field_value_list
   from contentpropertyassignments cp
   group by cp.LIBRARYCONTENTID, cp.CONTENTPROPERTYNAME     
), packedProps as (
   select
   lc.ID as LIBRARYCONTENTID,
   objectagg(ppv.CONTENTPROPERTYNAME::variant, ppv.field_value_list::variant) as d
   from LIBRARYCONTENTS lc
   left join packedPropValues ppv on lc.ID = ppv.LIBRARYCONTENTID 
   group by  lc.ID      
)
   select
   lc.CREATEDAT, lc.TYPE, lc.FORMAT, lc.ID, lc.ISDELETED, lc.ISPUBLISHED, lc.LATESTLIBRARYCONTENTVERSIONCREATEDAT, lc.LATESTLIBRARYCONTENTVERSIONID, lc.LATESTLIBRARYCONTENTVERSIONSIZE, lc.LIBRARYURL, lc.MODIFIEDAT, lc.NAME, lc.NEWSCENTERURL, lc.DOCCENTERURL, lc.OWNERID, lc.OWNERUSERNAME, lc.PREVIEWIMAGEID, lc.PREVIEWIMAGEURL, lc.PUBLISHEDVERSIONEXPIRESAT, lc.TEAMSITEID, lc.TEAMSITENAME, lc.THUMBNAILIMAGEID, lc.THUMBNAILIMAGEURL, lc.VERSION, lc.DESCRIPTION, lc.ISCHECKEDOUT, lc.SHORTID, lc.PARENTFOLDERLIBRARYCONTENTID, lc.LIBRARYPATH,    
   pp.d:"Asset Type"::string as "Asset Type", pp.d:"Sales Stage"::string as "Sales Stage", 
   inner join packedProps pp on pp.LIBRARYCONTENTID = lc.id 
;

Now that the SQL is generated, you need to copy it to your clip board. To do this, simply click on the cell in the query results and it will open a dialog with the full text of the result. Hover your mouse over the top right corner of the dialog and click the Copy button to copy the text to your clipboard.

Step 3 - Run the query

Now that you have the SQL from Step 2, all you need to do is execute it and it will produce your complete library inventory with content properties as columns with comma delimited values.

👍

Execution Environment

While these screenshots and examples all ran from the Snowflake UI, you can execute these queries from any Snowflake compatible query interface or workflow automation tool to fully automate execution and unload of this data.

❗️

100MB Limit!

Note, the Snowflake UI has a 100MB limit on CSV downloads. If your library inventory exceeds 100MB, you will either need to have the query unload results to Azure Blob (documented here), Amazon S3 (documented here), or Google Storage (documented here) or you will need to use a different query interface tool.