jsonb SQL accessor generator

jsonbSQLAccessorGenerator.py

import json 
import typing
import sys

'''
What this does:
Take in a representative jsonb blob and generate hierarchical sql accessors for all its fields. 

Usage: 
  python3 jsonbSQLAccessorGenerator.py 'representative json blob'
'''


def key_path_as_sql(path: list[str]):
  '''
  Format as a hierarchially indented sql getter from jsonb
  '''
  p = ", ".join([item for item in path])
  sqlStmt = """{whitespace}workflow_state#>>'{{{path}}}'""".format(whitespace = "  "*(len(path)-1), path=p)
  return sqlStmt


def path_builder(obj: dict, current_path: list = [], accum_paths = []):
  '''
  Extract the JSON structure into paths for Max-approved SQL jsonb extraction. 
  '''
  for key in obj.keys():
    if isinstance(obj[key], dict): 
      path_builder(obj[key], current_path + [key], accum_paths = accum_paths)
    else: 
      accum_paths.append(current_path+[key])
  return accum_paths

json_string = json_string = sys.argv[1]
json_data = json.loads(json_string)


paths = path_builder(json_data)

sql_stmts = [key_path_as_sql(p) for p in paths]
print(",\n".join(stmt for stmt in sql_stmts))