How to fix SQL/JSON scalar required in PostgreSQL

SELECT JSON_VALUE(jsonb '{"name": "John", "age": 30}', '$.name');
-- Returns: "John" (scalar string)

SELECT JSON_QUERY(jsonb '{"user": {"name": "John", "age": 30}}', '$.user');
-- Returns: {"name": "John", "age": 30} (object)

SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0]');
-- ERROR: SQL/JSON scalar required - $[0] is an object

SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0].name');
-- Returns: "Alice"

SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0].id');
-- Returns: 1

SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $');
-- ERROR: SQL/JSON scalar required - $ refers to the entire array

-- Get first element
SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $[0]');
-- Returns: "apple"

-- Get element by index
SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $[1]');
-- Returns: "banana"

-- Get nested scalar in array element
SELECT JSON_VALUE(
  jsonb '[{"color": "red"}, {"color": "yellow"}]',
  'strict $[0].color'
);
-- Returns: "red"

-- This fails because JSON_VALUE expects scalars
SELECT JSON_VALUE(
  jsonb '{"user": {"name": "John", "details": {"age": 30, "city": "NYC"}}}',
  '$.user.details'
);
-- ERROR: SQL/JSON scalar required - details is an object

-- Use JSON_QUERY to extract the object
SELECT JSON_QUERY(
  jsonb '{"user": {"name": "John", "details": {"age": 30, "city": "NYC"}}}',
  '$.user.details'
);
-- Returns: {"age": 30, "city": "NYC"}

-- To then extract scalar from it, use JSON_VALUE on the result
SELECT JSON_VALUE(
  jsonb '{"user": {"name": "John", "details": {"age": 30, "city": "NYC"}}}',
  '$.user.details.age'
);
-- Returns: 30

SELECT JSON_VALUE(jsonb '[1, 2, 3]', 'strict $');
-- ERROR: SQL/JSON scalar required - strict mode rejects array

SELECT JSON_VALUE(jsonb '{"a": [1, 2]}', 'strict $.a');
-- ERROR: SQL/JSON scalar required - $.a is an array, not scalar

SELECT JSON_VALUE(jsonb '[1, 2, 3]', 'lax $');
-- Returns: NULL - lax mode adapts and returns null for array

SELECT JSON_VALUE(jsonb '{"a": [1, 2]}', 'lax $.a');
-- Returns: NULL - lax mode returns null when structural type doesn't match

SELECT JSON_VALUE(
  jsonb '[1, 2, 3]',
  'strict $'
  ERROR ON ERROR
);
-- ERROR explicitly raised

SELECT JSON_VALUE(
  jsonb '[1, 2, 3]',
  'strict $'
  DEFAULT 'not-a-scalar' ON ERROR
);
-- Returns: "not-a-scalar" (error handled gracefully)

-- Check if a path exists and would return a scalar
SELECT jsonb '{"name": "Alice", "hobbies": ["reading", "gaming"]}' @? '$.name';
-- Returns: true

SELECT jsonb '{"name": "Alice", "hobbies": ["reading", "gaming"]}' @? '$.hobbies';
-- Returns: true (path exists but might not be scalar)

-- Better approach: explicitly validate before JSON_VALUE
WITH data AS (
  SELECT jsonb '{"user": {"name": "John", "age": 30}}' as doc
)
SELECT
  CASE
    WHEN doc @? '$.user.name' THEN JSON_VALUE(doc, '$.user.name')
    ELSE NULL
  END as name
FROM data;
-- Safely extracts name if it exists

SELECT
  id,
  JSON_VALUE(config, '$.timeout' RETURNING integer DEFAULT 30 ON ERROR) as timeout,
  JSON_VALUE(config, '$.host' DEFAULT 'localhost' ON ERROR) as host
FROM settings
WHERE status = 'active';

-- Test JSON structure
SELECT jsonb_pretty(jsonb '{"users": [{"id": 1, "name": "Alice"}]}');

-- Test path exists
SELECT jsonb '{"users": [{"id": 1, "name": "Alice"}]}' @? '$.users[0].name';

-- Test JSON_QUERY first (more lenient)
SELECT JSON_QUERY(
  jsonb '{"users": [{"id": 1, "name": "Alice"}]}',
  '$.users[0]'
);
-- If this works, then test JSON_VALUE on the specific field:

SELECT JSON_VALUE(
  jsonb '{"users": [{"id": 1, "name": "Alice"}]}',
  '$.users[0].name'
);