Convert JSON String to Key-Value

[Saraabdi955]

Hi,

I have a json string (attached), and I want to convert it to key-value format like this:

 How to do this?

company_number	key	value
9145694	address	{"address_line_1":"St. Andrews Road","country":"England","locality":"Henley-On-Thames","postal_code":"RG9 1HP","premises":"2"}
9145694	address	ceased_on: "2018-05-14"
9145694	address	country_of_residence:"England"
9145694	date_of_birth	{"month":2,"year":1977}
9145694	date_of_birth	etag:"3b8caf795c03af63921e381f7bb8300a51ebb73d"
9145694	date_of_birth	kind:"individual-person-with-significant-control"

[apathetichell · Accepted solution]

You posted a .png - that's totally pointless. also - include multiple entries so we can show you how to split this. You won't get help if you create more work for us. The short is you'll use json parse tool multiple times. You'll start by parsing to retrieve the companies - and they'll parse child objects as needed. you may need to use a recordid first - you may be able to use a formual tool to use regex to extract the item number - and then rejoin. I can't really tell with what you've posted.

[KGT · Accepted solution]

Your first bet is going to be the JSON Parse Tool. After that, it's usually a mix of Text To Columns, Cross Tab etc.

The JSON looks pretty standard, so hopefully it pops out pretty easily.