Extract Date from JSON Column

[stevelp]

I’m struggling to extract the start and end date from a JSON column after I’ve already performed some JSON manipulations on it.

After I pull from the data from the API, I apply a JSON path node that’s configured like this:

Most of the columns are extracted ok, but then I noticed the date range is formatted a little differently:

Then I do an Ungroup node, followed by another JSON path node, where I’ve manually clicked on each item in the JSON-cell preview and clicked “Add JSON path”.

After doing that, all of the cells seem to come out fine, except for the date range, which is now a string column which has contents that look like this:

{start={month=6, day=6, year=2020}, end={month=6, day=6, year=2020}}

Is there a better way to do my JSON nodes to get this to extract into separate columns? Or should I just try to do some kind of manipulations to extract the dates from this string field?

[Matt_D]

Hi @stevelp,

$[‘dateRange’].[‘start’].[‘day’]
$[‘dateRange’].[‘start’].[‘month’]
$[‘dateRange’].[‘start’].[‘year’]

Replace start with end for your end dates e.g.

$[‘dateRange’].[‘end’].[‘day’]
etc

This should be the right JSON paths to get the values into columns.

Thanks,

Matt

[stevelp]

Thanks @Matt_D! Worked perfectly!