Grabbing a particular value from a json type in sql

Question

I have these varies entries in a column that look like this

[{'bus': '008', 'device': '002', 'bDeviceClass': '0 (Defined at Interface level)',  'iSerial': '3 30J155001549',... }]

I want to grab the iSerial value 30J155001549 but I am not sure exactly how to do this in sql. Any recommendations are greatly appreciated. The database we are using is Redshift.

I have tried this

SELECT json_extract_path_text(
    json_extract_array_element_text(device_usb,0), 'iSerial'
)
FROM table
LIMIT 1;

But I am getting this error

[XX000][500310] [Amazon](500310) Invalid operation: JSON parsing error Details: ----------------------------------------------- error: JSON parsing er ...

Here is a full example

[{'bus': '004', 'device': '002', 'bDeviceClass': '0 (Defined at Interface level)', 'bInterfaceClass': '3 Human Interface Device', 'bInterfaceProtocol': '2 Mouse', 'idVendor': '0x413c Dell Computer Corp.', 'idProduct': '0x301a', 'iManufacturer': '1 PixArt', 'iProduct': '2 Dell MS116 USB Optical Mouse', 'iSerial': '0', 'MaxPower': '100mA'}, {'bus': '003', 'device': '014', 'bDeviceClass': '224 Wireless', 'bInterfaceClass': '224 Wireless', 'bInterfaceProtocol': '1 Bluetooth', 'idVendor': '0x8087 Intel Corp.', 'idProduct': '0x07dc', 'iManufacturer': '0', 'iProduct': '0', 'iSerial': '0', 'MaxPower': '100mA'}, {'bus': '003', 'device': '010', 'bDeviceClass': '0 (Defined at Interface level)', 'bInterfaceClass': '3 Human Interface Device', 'bInterfaceProtocol': '0 None', 'idVendor': '0x413c Dell Computer Corp.', 'idProduct': '0x2113', 'iManufacturer': '0', 'iProduct': '2 Dell KB216 Wired Keyboard', 'iSerial': '0', 'MaxPower': '100mA'}, {'bus': '005', 'device': '002', 'bDeviceClass': '0 (Defined at Interface level)', 'bInterfaceClass': '3 Human Interface Device', 'bInterfaceProtocol': '0 None', 'idVendor': '0x0c2e Metro', 'idProduct': '0x09cf', 'iManufacturer': '1 Honeywell Imaging & Mobility', 'iProduct': '2 CCB02', 'iSerial': '8 16197B4974', 'MaxPower': '500mA'}]

Answer 1

Research the database's documentation for SQL functions

See the reference-documentation of Amazon Redshift SQL about JSON functions:

json_extract_path_text('json_string', 'path_elem' [,'path_elem'[, …] ] [, null_if_invalid ] )

This function can be applied to extract a field as text. You have two pass 2 arguments:

• the field with your JSON-content (1st parameter)
• the JSN-path as string (2nd parameter)

Applied

Something like:

SELECT
json_extract_path_text(
    json_extract_array_element_text(jsonContainingField,0), 'iSerial'
)
FROM table

assumed to give you the column name iSerial from the first (0 since the index is zero-based!) element of your given array. If the column contains below JSON in valid format, then the result should be as expected: 3 30J155001549

[
  {
    'bus': '008', 
    'device': '002', 
    'bDeviceClass': '0 (Defined at Interface level)',
    'iSerial': '3 30J155001549'
   }
]

Troubleshooting: JSON parse error

The error-message you got seems to indicate that the JSON passed to the function (or the contents of the column) is no valid JSON.

See similar: [XX000][500310] [Amazon](500310) Invalid operation: Parsed manifest is not a valid JSON object

Did you try a dry-run by passing a constant JSON-string to the function like in the official doc's JSON-function examples?

As far as I know valid JSON requires fields and text-values to be enclosed in double quotes like "fieldName": "textValue" instead of 'fieldName': 'textValue'.

Validate JSON prior

You can check each JSON with some free tools on your computer on the web. For example JsonLint.com reports the given JSON as invalid!

Validate JSON in Redshift

Use the IS_VALID_JSON function to check which rows contain invalid JSON, like SELECT device_usb, IS_VALID_JSON(device_usb) FROM table.

For each invalid results (where false is returned), you can then try to update the value by using an UPDATE statement with the suitable text- or String-functions.

Example:

UPDATE table SET device_usb = TRANSLATE(device_usb, ''', '"') WHERE IS_VALID_JSON(device_usb) = 'false'

Invalid: single quotes

[{
    'bus': '004'
}]

A slight replacement of all single-quotes ' by required double-quotes " made it valid.

Valid: double quotes

[{
    "bus": "004"
}]

Answer 2

You can extract JSON by:

select * from OPENJSON('your JSON string')