SQLite json_error_position() Function

Summary: in this tutorial, you will learn how to use the SQLite json_error_position() function to return the character position of the first syntax error in a JSON string.

Introduction to the SQLite json_error_position() function

When working with JSON data, you may encounter JSON syntax errors.

To identify the exact location of the error within a JSON string, you can use the json_error_position() function.

The json_error_position() function returns the character position of the first syntax error in a JSON string.

Here’s the syntax of the json_error_position() function:

json_error_position(json_string)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_string is the JSON string that you want to check for errors.

SQLite json_error_positition() function examples

Let’s take some examples of using the json_error_position() function.

1) Checking well-formed JSON strings

SELECT json_error_position('{"name":"John", "age":30}') position;Code language: SQL (Structured Query Language) (sql)

Output:

position
--------
0Code language: SQL (Structured Query Language) (sql)

The function returns 0, indicating that the input JSON string has no error.

2) Checking malformed JSON strings

SELECT json_error_position('{"name":"John", "age":30') position;Code language: SQL (Structured Query Language) (sql)

Output:

position
--------
25Code language: SQL (Structured Query Language) (sql)

In this example, the JSON string does not have the closing brace. Therefore, the json_error_position() function returns the position of the error, which is 25 in this case.

3) Using SQLite json_error_position() function with table data

First, create a table called user_profiles:

CREATE TABLE user_profiles(
    id INTEGER PRIMARY KEY,
    profile_data JSON NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the user_profiles table:

INSERT INTO user_profiles (profile_data) 
VALUES
('{"username": "john_doe", "email": "[email protected]", "full_name": "John Doe", "role": "admin", "created_at": "2024-04-27T08:00:00Z"}'),
('{"username": "alice_smith", "email": "[email protected]", "full_name": "Alice Smith", "role": "editor", "created_at": "2024-04-27T09:15:00Z"}'),
('{"username": "bob_jones", "email": "[email protected]", "full_name": "Bob Jones", "role": "user", "created_at": "2024-04-27T10:30:00Z"}'),
('malformed JSON here');Code language: SQL (Structured Query Language) (sql)

Third, use the json_error_position() function to list all user profiles and highlight any errors if present:

SELECT
  id,
  profile_data,
  CASE
    WHEN json_error_position (profile_data) = 0 THEN 'Valid'
    ELSE 'Invalid JSON at position ' || json_error_position (profile_data)
  END AS json_status
FROM
  user_profiles;Code language: SQL (Structured Query Language) (sql)

Output:

id  profile_data                                                  json_status
--  ------------------------------------------------------------  --------------------------
1   {"username": "john_doe", "email": "[email protected]", "full_  Valid
    name": "John Doe", "role": "admin", "created_at": "2024-04-2
    7T08:00:00Z"}

2   {"username": "alice_smith", "email": "[email protected]", "f  Valid
    ull_name": "Alice Smith", "role": "editor", "created_at": "2
    024-04-27T09:15:00Z"}

3   {"username": "bob_jones", "email": "[email protected]", "full_  Valid
    name": "Bob Jones", "role": "user", "created_at": "2024-04-2
    7T10:30:00Z"}

4   malformed JSON here                                           Invalid JSON at position 1Code language: SQL (Structured Query Language) (sql)

Summary

• Use the json_error_position() function to check a JSON string and return the character position of the first syntax error.