select x.pol_num, x.pol_name
from json_table('{data:[{"id":"abcdefg","sfid":"hijklmnop","audits":[{"policy_num":"10","policy_name":"Sally"},{"policy_num":"20","policy_name":"John"}]},' ||
' {"id":"azclkjd","sfid":"sldkfjd","audits":null}]}', '$.data[ * ].audits[ * ]'
columns(id for ordinality,
pol_num path '$.policy_num',
pol_name path '$.policy_name')) x
| POL_NUM | POL_NAME | 10 | Sally | 20 | John | - | - |
|---|