jsonpython-3.xpandasjson-normalizejson-flattener

Flatten a nested JSON?


I am trying to flatten the following JSON and flatten it hierarchically: https://justpaste.it/6e60p

I am using pandas json_normalize function to do this but I am bit stuck.

pd.json_normalize(test_json['result'])

Gives me 2 columns with nested dicts. I am trying to use record_path=['userDetails'] but then it opens only the user part.

Please advise what is the best practice to flatten the JSON hierarchically?

test_json = {
 "success": True,
 "error": 'null',
 "errorObject": None,
 "result": [
 {
 "requestId": 45,
 "userIds": [
 4
 ],
 "userDetails": [
 {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 ],
 "requestColumnGrants": [
 {
 "id": 57,
 "creationTime": 1652083406187,
 "lastUpdateTime": 1652083407853,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 1,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "nationkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "nationkey",
 "description": None,
 "tags": [
 {
 "id": 1,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "pii",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 0,
 "ordinalOverride": None
 },
 "operationType": "FULL_MASKING",
 "operationPayload": "{\"letter\":\"*\"}",
 "policyRecommendedOperationType": "FULL_MASKING",
 "policyRecommendedOperationPayload": "{\"letter\":\"*\"}",
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 56,
 "creationTime": 1652083406165,
 "lastUpdateTime": 1652083406165,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 2,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "name",
 "rawType": "STRING",
 "type": "UNKNOWN",
 "trinoName": "name",
 "description": None,
 "tags": [
 {
 "id": 2,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "first_name",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 1,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 55,
 "creationTime": 1652083406141,
 "lastUpdateTime": 1652083406141,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 3,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "regionkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "regionkey",
 "description": None,
 "tags": [
 {
 "id": 5,
 "creationTime": 1652080042000,
 "lastUpdateTime": 1652080042000,
 "name": "region",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 2,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 }
 ],
 "workflowValues": [
 {
 "requestId": 45,
 "requestWorkflowProgressionId": 106,
 "name": "f3624cb8-cebd-4608-9d9a-60c5b21adbd3.firstName",
 "value": "dcasd",
 "type": "String",
 "source": "Form",
 "id": 1,
 "creationTime": 1652083422000,
 "lastUpdateTime": 1652083422000
 },
 {
 "requestId": 45,
 "requestWorkflowProgressionId": 106,
 "name": "f3624cb8-cebd-4608-9d9a-60c5b21adbd3.lastNam",
 "value": "dsadw",
 "type": "String",
 "source": "Form",
 "id": 2,
 "creationTime": 1652083422000,
 "lastUpdateTime": 1652083422000
 }
 ]
 },
 {
 "requestId": 44,
 "userIds": [
 4
 ],
 "userDetails": [
 {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 ],
 "requestColumnGrants": [
 {
 "id": 54,
 "creationTime": 1652082684949,
 "lastUpdateTime": 1652082686821,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 1,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "nationkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "nationkey",
 "description": None,
 "tags": [
 {
 "id": 1,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "pii",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 0,
 "ordinalOverride": None
 },
 "operationType": "FULL_MASKING",
 "operationPayload": "{\"letter\":\"*\"}",
 "policyRecommendedOperationType": "FULL_MASKING",
 "policyRecommendedOperationPayload": "{\"letter\":\"*\"}",
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 53,
 "creationTime": 1652082684927,
 "lastUpdateTime": 1652082684927,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 2,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "name",
 "rawType": "STRING",
 "type": "UNKNOWN",
 "trinoName": "name",
 "description": None,
 "tags": [
 {
 "id": 2,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "first_name",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 1,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 52,
 "creationTime": 1652082684905,
 "lastUpdateTime": 1652082684905,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 3,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "regionkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "regionkey",
 "description": None,
 "tags": [
 {
 "id": 5,
 "creationTime": 1652080042000,
 "lastUpdateTime": 1652080042000,
 "name": "region",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 2,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 }
 ],
 "workflowValues": []
 },
 {
 "requestId": 40,
 "userIds": [
 4
 ],
 "userDetails": [
 {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 ],
 "requestColumnGrants": [
 {
 "id": 42,
 "creationTime": 1651412063835,
 "lastUpdateTime": 1651412065727,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 1,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "nationkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "nationkey",
 "description": None,
 "tags": [
 {
 "id": 1,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "pii",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 0,
 "ordinalOverride": None
 },
 "operationType": "FULL_MASKING",
 "operationPayload": "{\"letter\":\"*\"}",
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 41,
 "creationTime": 1651412063807,
 "lastUpdateTime": 1651412063807,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 2,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "name",
 "rawType": "STRING",
 "type": "UNKNOWN",
 "trinoName": "name",
 "description": None,
 "tags": [
 {
 "id": 2,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "first_name",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 1,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 40,
 "creationTime": 1651412063779,
 "lastUpdateTime": 1651412063779,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 3,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "regionkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "regionkey",
 "description": None,
 "tags": [
 {
 "id": 5,
 "creationTime": 1652080042000,
 "lastUpdateTime": 1652080042000,
 "name": "region",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 2,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 }
 ],
 "workflowValues": []
 },
 {
 "requestId": 39,
 "userIds": [
 4
 ],
 "userDetails": [
 {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 ],
 "requestColumnGrants": [
 {
 "id": 39,
 "creationTime": 1651411857024,
 "lastUpdateTime": 1651411858669,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 1,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "nationkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "nationkey",
 "description": None,
 "tags": [
 {
 "id": 1,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "pii",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 0,
 "ordinalOverride": None
 },
 "operationType": "FULL_MASKING",
 "operationPayload": "{\"letter\":\"*\"}",
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 38,
 "creationTime": 1651411856994,
 "lastUpdateTime": 1651411856994,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 2,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "name",
 "rawType": "STRING",
 "type": "UNKNOWN",
 "trinoName": "name",
 "description": None,
 "tags": [
 {
 "id": 2,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "first_name",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 1,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 37,
 "creationTime": 1651411856970,
 "lastUpdateTime": 1651411856970,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 3,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "regionkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "regionkey",
 "description": None,
 "tags": [
 {
 "id": 5,
 "creationTime": 1652080042000,
 "lastUpdateTime": 1652080042000,
 "name": "region",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 2,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 }
 ],
 "workflowValues": []
 },
 {
 "requestId": 38,
 "userIds": [
 4
 ],
 "userDetails": [
 {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 ],
 "requestColumnGrants": [
 {
 "id": 36,
 "creationTime": 1651411738066,
 "lastUpdateTime": 1651411739933,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 1,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "nationkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "nationkey",
 "description": None,
 "tags": [
 {
 "id": 1,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "pii",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 0,
 "ordinalOverride": None
 },
 "operationType": "FULL_MASKING",
 "operationPayload": "{\"letter\":\"*\"}",
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 35,
 "creationTime": 1651411738036,
 "lastUpdateTime": 1651411738036,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 2,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "name",
 "rawType": "STRING",
 "type": "UNKNOWN",
 "trinoName": "name",
 "description": None,
 "tags": [
 {
 "id": 2,
 "creationTime": 1651048111000,
 "lastUpdateTime": 1651048111000,
 "name": "first_name",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 1,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 },
 {
 "id": 34,
 "creationTime": 1651411738012,
 "lastUpdateTime": 1651411738013,
 "schemaInfoDetails": {
 "id": 1,
 "displayName": "adam-test-collibra"
 },
 "tableInfoDetails": {
 "id": 1,
 "displayName": "nation"
 },
 "columnInfoDetails": {
 "id": 3,
 "creationTime": 1651047171000,
 "lastUpdateTime": 1652080042000,
 "name": "regionkey",
 "rawType": "INT64",
 "type": "UNKNOWN",
 "trinoName": "regionkey",
 "description": None,
 "tags": [
 {
 "id": 5,
 "creationTime": 1652080042000,
 "lastUpdateTime": 1652080042000,
 "name": "region",
 "tagLevel": "COLUMN",
 "riskFactor": "NONE"
 }
 ],
 "ordinal": 2,
 "ordinalOverride": None
 },
 "operationType": "NONE",
 "operationPayload": None,
 "policyRecommendedOperationType": None,
 "policyRecommendedOperationPayload": None,
 "riskFactor": "NONE",
 "creatorUserId": 4,
 "creatorUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 },
 "lastUpdateUserId": 4,
 "lastUpdateUserDetails": {
 "displayName": "Admin Demo",
 "profileImageUrl": None,
 "roles": [
 "ADMIN"
 ],
 "countryIso": "IL",
 "id": 4,
 "creationTime": 1650970312000,
 "lastUpdateTime": 1651046585000
 }
 }
 ],
 "workflowValues": []
 }
 ],
 "paging": {
 "start": 0,
 "limit": 20,
 "total": 5,
 "hasMore": False
 }
}

Solution

  • flatten_json is a library now, so you can do this. It'll give you 160 columns

    from flatten_json import flatten
    dic_flattened = (flatten(d, '.') for d in test_json['result'])
    df = pd.DataFrame(dic_flattened)
    
    df.shape
    (5, 160)